Two keyed feilds with referential integrity

[Guest]

Hi,

I would like to create two tables, a master and detail, containing order
information.

I've created a header table with two primary keys, one text type and another
of auto-number. This works fine and I can create and view the header records.

I would also like to create a detail table with reference to the two primary
keys.

Example:

Header Record:

U4AF01 1
U4AF01 2

Detail Record:

Part A 10 U4AF01 1
Part B 10 U4AF01 1
Part A 10 U4AF01 2

Can anyone please help with this?

regards

Marcus Lloyd

 

[John Vinson]

Hi,

I would like to create two tables, a master and detail, containing order
information.

I've created a header table with two primary keys, one text type and another
of auto-number. This works fine and I can create and view the header records.

Ummm... No. You didn't.

A Table can have ONLY ONE primary key. That key might consist of two
(or five, or even ten) fields, but it's only *one* Primary Key. In
fact Access will not permit you to create two primary keys in a table.

Also, if you have an Autonumber field, it is already unique. Combining
it with the text field doesn't make it any "uniquer"; it would be a
waste of effort to create a two-field key in which one of the fields
is an autonumber - just use the Autonumber itself, since it is already
unique.

I would also like to create a detail table with reference to the two primary
keys.

Example:

Header Record:

U4AF01 1
U4AF01 2

Detail Record:

Part A 10 U4AF01 1
Part B 10 U4AF01 1
Part A 10 U4AF01 2

Can anyone please help with this?

Well... Don't. If you have a two-field primary key (not including an
autonumber), simply create the detail table with two fields of
matching datatypes; in the relationship window, drag each field from
the header table to the corresponding child table field; you'll end up
with two join lines on the display. You should set referential
integrity on both of these join lines.

John W. Vinson[MVP]