Key field question and linking tables.

[larpup]

I have a table (TABLE1) with a compound key (two fields). A CustomerID
field and an Order Number field. (Parent Table). Our Customers can and
do have duplicate order numbers.

I have another table (TABLE2) that need be linked to the first table.
(Child Table)

The way I would do this in my old database is;

Have a form based on TABLE 1 with fields from TABLE 2 also on the form.

After both key fields (from TABLE 1) are entered into the form, I would
have a hidden field concatenating both fields. This field would be
linked to an indexed field in TABLE 2.

What is the best way to accomplish this in Access without having a
concatenated field in TABLE 1 (which I need to link to TABLE 2). Is
AutoNumber a reliable way to accomplish this in a multiuser
enviornment? I was hoping to have a meaning full relationship other
than autonumber.

Any recommendations will be greatly appreciated.

Lar

 

[Rick Brandt]

I have a table (TABLE1) with a compound key (two fields). A CustomerID
field and an Order Number field. (Parent Table). Our Customers can and
do have duplicate order numbers.

I have another table (TABLE2) that need be linked to the first table.
(Child Table)

The way I would do this in my old database is;

Have a form based on TABLE 1 with fields from TABLE 2 also on the form.

After both key fields (from TABLE 1) are entered into the form, I would
have a hidden field concatenating both fields. This field would be
linked to an indexed field in TABLE 2.

What is the best way to accomplish this in Access without having a
concatenated field in TABLE 1 (which I need to link to TABLE 2). Is
AutoNumber a reliable way to accomplish this in a multiuser
enviornment? I was hoping to have a meaning full relationship other
than autonumber.

Any recommendations will be greatly appreciated.

You do not need a concatenated single field and creating one would be a bad
idea. Just link on TWO fields. You can enforce relationships on up to ten
fields in Access. In your case Table2 should contain BOTH a CustomerID and an
OrderNumber field.

The alternative is to add a surrogate key field like an AutoNumber to Table1 so
that you can use that single field to link to Table2. I generally prefer a
natural key, but also don't like relating tables on multiple fields. Two fields
is usually my limit. Any more than that and I usually go with a surrogate key.

 

[Tim Ferguson]

You do not need a concatenated single field and creating one would be
a bad idea. Just link on TWO fields. You can enforce relationships
on up to ten fields in Access. In your case Table2 should contain
BOTH a CustomerID and an OrderNumber field.

.... and an OrderLine field, with the PK being made up of all three.

Just in case it wasn't clear from the above <g>!


All the best


Tim F

 

[larpup]

Thanks Rick,

I didn't know that you can link on more than one field. Thanks.
Lar