Key for Detail Table

[Kevin Sprinkel]

For a given project (tblProjects: KF = Text 5), we have
several estimates (tblEstimates), numbered sequentially by
the user.

Originally, I designed tblEstimates with a compound key,
the foreign key to tblProjects and the Estimate number.
On this forum, however, I frequently read the
recommendation to simply generate an autonumber field for
the detail table key.

Please describe the issues, if any, between these two
approaches.

Thank you.

Kevin Sprinkel
Becker & Frondorf

 

[Scott McDaniel]

Compound keys are fine, especially if they're used to guarantee unique
values, but many advocate the use of "surrogate" keys in the form of
Autonumber fields. The AN fields would be used in addition to your other key
or indices, and would be used to link your tables together.

For example, if I had a Customer table and a Contacts table (with a 1 - m
relationship), my Unique Value in the Customer table may be a combination of
FirstName, LastName, and StreetAddress ... if I used those three fields as
my Primary Key, then in order to link my Customer table with my Contacts
table, I'd have to include all 3 of those fields in my Contacts table. This
approach would certainly work, but it makes for some interesting query
expressions. I find it easier to add an AutoNumber field to my Customers
table and use the Autonumber field in my Contacts table to relate the two.
You would still use the 3 fields mentioned above to gurantee uniqueness by
adding an index that includes those 3 fields and setting the Unique property
of the index = Yes.

 

[Kevin Sprinkel]

You would still use the 3 fields mentioned above to
gurantee uniqueness by adding an index that includes those
3 fields and setting the Unique property of the index =
Yes.

Thanks, this approach makes linking easier AND maintains
the guaranteed uniqueness of the 3-field combination.
Great.

Can you please describe how to add this index manually?
I've never done one of these.

Best regards.
Kevin Sprinkel
Becker & Frondorf

 

[Scott McDaniel]

Open your table in design view and click View - Indexes. To build a
multi-field index, type a name for your index in the left column, then
select the fields to be included in the index in the middle column (the
"Field Name" column). Note that to build a multi-field index, you do NOT
specify anything in the "Index Name" column for each field, only for the
first field. So, to specify an index named "Customer" which contains the
FirstName, LastName, and Street field, your index dialog would look
something like this:

Index Name Field Name
--------------------------------
Customer FirstName
LastName
Street

Make sure to select "Unique" if you want to use this index to guarantee
uniqueness.

Hopefully this will format correctly ... but you should get the gist of what
to do.

 

[Kevin Sprinkel]

Thanks again, Scott. Works perfectly.