Lee Ann

At the risk of (re-)igniting "religious wars", I'll point out that arbitrary
primary keys (e.g., Autonumber) work well when there is no reasonable
"natural" key.

An example of a reasonable natural key can be found in another newsgroup
thread concerning using Stock Symbols as "natural" primary keys.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Lee Ann" wrote in message
...
I've followed the direction from Steve and Karl. Just a few follow-up
questions - I've set up tables according to Steve. When trying to link
similar fields between the tables (EstablishmentID), it doesn't allow me
to
do it unless I set up the foreign key (if that's the correct word) as
Number/Long Integer, as suggested by Karl. Is this always the way it's
supposed to be done? If I don't use the number/LI, but yet have the same
data type in both tables, it tells me it can't match because the data
types
are not the same.

Thanks for the assistance - it seems the more I learn, the more questions
there are.



"KARL DEWEY" wrote:

Date of Offense - Time of Offense - Day of Week
Use single DateTime field and derive the parts as needed.

Since I will be dealing with several establishments, I'm assuming I'd
want this in a seperate table.
No, one table for incidents.

Use EstablishmentNameID as an Autonumber primary key field in the
TblEstablishment and EstablishmentNameID - Number Long Integer - as
foreign
key in the incident table.

Create a one-to-many relationship between TblEstablishment and incident,
selecting Referential Integerity and Cascade Update.

Use a combo on your incident form to pick the Establishment from
TblEstablishment.

--
Build a little, test a little.


"Lee Ann" wrote:

I've searched through these forums and read alot of discussions
regarding not
using a lookup in a table. Instead, one should put that information in
another table and then use a combo box (using the wizard) to create
that
field.

I'm getting confused trying to figure out a few things with this method
and
I'm hoping for some direction.

My database will be used to capture enforcement information regarding a
particular crime in my area. This crime involved establishments, the
criminals and certain information related to the criminals. Rather
than lay
out all of the specifics, if I can figure out how to relate two of the
tables
together, I should be fine. So, I would assume I need a table for the
basic
information:

Tbl Incident
Date of Offense
Time of Offense
Day of Week
Establishment Name
Subject Name

Since I will be dealing with several establishments, I'm assuming I'd
want
this in a seperate table. I'd also want to include an address and a
specific
area for this:

Tbl Establishment
Establishment Name
Establishment Address
Establish Area

I know I should be connecting the Tbl Establishment with the Tbl
Incident
and there should be like titles in both of the tables on which to
match.
I've looked at databases created by those with alot of knowledge in
Access
and I notice there's usually an ID field in each table
(EstablishmentNameID -
for instance). This is where the confusion is coming in: Should
EstablishmentNameID be the primary key in this table as opposed to ID
with
autonumber? If it's not the primary key, I get an error message that
there's
no unique index between the two.

Second point of confusion, assume the Establishment table merely
contained
the name of the establishment. By using the wizard, I put a combo box
on the
form to capture just the name and then tell it to store the
establishment
name in the Incident table (under Establishment Name) and that works
fine.
However, we have many of the same establishment names with different
addresses and I need the address and areas included. I'm assuming the
combo
box is the wrong choice with this, as it only allows me to store the
information from 1 field in my Incident table.

I apologize for the lengthy post and I appreciate any assistance I can
get.
Thanks in advance.