Creating Relationships between tables

[Lee Ann]

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.

 

[Jeff Boyce]

Lee Ann

I think you missed the suggestion ...

You don't use the wizard to create the lookup in a field IN THE TABLE. You
can use a wizard to create a control IN A FORM. That's the preferred
approach.

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.

 

[Steve]

I recommend that the primarry key of 99.99% of tables be the table name with
"ID" at the end. Make the primary key autonumber. Do this and you will never
go wrong! So in your case the primary key of your incident table would be
IncidentID and the primary key of your establishment table would be
EstablishmentID. Further, It is likely that a criminal will over time be
involved in more than one incident. So I recommend that you also have a
criminal table. This will allow you to easy create a rap sheet any time for
a particular criminal. I don't know what your Area field is but most likely
you also nee an Area table.Finally it is recommended practice not to use
spaces in any Access object names (tables, fields, queries, forms reports,
etc). So now your tables should look like:
TblCriminal
CriminalID
FirstName
LastName
<criminal fields>

TblArea
AreaID
<area fields>

TblEstablishment
EstablishmentID
EstablishmentName
EstablishmentAddress
EstablishmentCity
EstablishmentState
EstablishmentZipcode
AreaID

Tbl Incident
IncidentID
DateofOffense
TimeofOffense
CriminalID
EstablishmentID

Note that I did not include day of week. Day of week can be easily
calculated from DateOfOffense.

Post back if you have any questions.

Steve
(e-mail address removed)

 

[KARL DEWEY]

Date of Offense - Time of Offense - Day of Week
Use single DateTime field and derive the parts as needed.
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.

 

[Lee Ann]

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.

 

[Steve]

Always use Autonumber for the primary key (table name + ID) and Number -
Long Integer for all foreign keys. When you create a new foreign key, notice
that Access assigns 0 as the default value. I like to reset the default
value to blank.

When createing tables, I always put the primary key (table name + ID) as the
first field. Foreign keys are then created in the field list from 2 on. Also
99.99% of the time I name the foreign key the same as its corresponding
primary key. Doing this you can look at the design of a table and
immediately pick out the primary key, all foreign keys and the tables that
all foreign keys are related to (from the name!).

Steve
(e-mail address removed)

 

[Jeff Boyce]

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.

 

[BruceM via AccessMonster.com]

I too will risk igniting conflict by saying natural keys make perfect sense
in some situations. An autonumber is a good choice much of the time, but I
wouldn't get locked into thinking it always needs to be used.

An autonumber is a type of Long Integer field. Linking fields must be of the
same data type (LI to LI, text to text, Double to Double, etc.), which is why
Long Integer must be used to link to autonumber. Number field is a sort of
umbrella term, but the specifics (Integer, Long Integer, Currency, Single,
Double, etc.) must be the same. You can't link an integer to currency, as
the latter may contain decimals and the former cannot.

Choose a naming convention that works for you. I too tend to use the table
name plus ID for the PK field. For instance, in tblEmployee, the PK is
EmployeeID. In the linked table I usually use the first letter or two of the
table name, then the linking field name. For instance, tblAward will have
AwardID as the PK, and A_EmployeeID as the linking field, also known as a
foreign key by some, but others hotly contest calling it a foreign key. The
point of using a variant of the field name is that I find it easier to tell
them apart in SQL and other code. This is my choice, but you may decide to
make a different choice. It helps to be consistent, especially if you are
working with others on the same project, but also for your own benefit when
you revisit a project after a year or two.

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.

Use single DateTime field and derive the parts as needed.

[quoted text clipped - 63 lines]

 

[Lee Ann]

Thanks for all the replies. It's a little clearer now.

I too will risk igniting conflict by saying natural keys make perfect sense
in some situations. An autonumber is a good choice much of the time, but I
wouldn't get locked into thinking it always needs to be used.

An autonumber is a type of Long Integer field. Linking fields must be of the
same data type (LI to LI, text to text, Double to Double, etc.), which is why
Long Integer must be used to link to autonumber. Number field is a sort of
umbrella term, but the specifics (Integer, Long Integer, Currency, Single,
Double, etc.) must be the same. You can't link an integer to currency, as
the latter may contain decimals and the former cannot.

Choose a naming convention that works for you. I too tend to use the table
name plus ID for the PK field. For instance, in tblEmployee, the PK is
EmployeeID. In the linked table I usually use the first letter or two of the
table name, then the linking field name. For instance, tblAward will have
AwardID as the PK, and A_EmployeeID as the linking field, also known as a
foreign key by some, but others hotly contest calling it a foreign key. The
point of using a variant of the field name is that I find it easier to tell
them apart in SQL and other code. This is my choice, but you may decide to
make a different choice. It helps to be consistent, especially if you are
working with others on the same project, but also for your own benefit when
you revisit a project after a year or two.

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.

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

[quoted text clipped - 63 lines]

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

--



.