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.