Multiple field primary key problem

[MFoskey]

I need to create a unique item id that pulls bits of info from 4 different
tables, then I want to use this conglomerate of information as a primary key
and assign transactions to it.

I would like to be able to pull each bit of info from a lookup list and have
the program weld all 4 pieces together in a seperate field to form the
primary key for me

Example is :
style 1234 from style table, color 56789 from color table, graphic number
AA1 from 1st graphic table, secondary graphic number BB2 from second graphic
table resulting in a unique product with an "item id" number of
1234-56789-AA1-BB2 (forming the primary key)

I want to then track each transaction belonging to the "specific" item
1234-56789-AA1-BB2.

 

[Jeff Boyce]

Perhaps you don't need to work quite that hard.

What if your "combined" table used an Autonumber Primary Key, and four
"Foreign Key" fields that point back to their respective lookup tables?
That way, you could "assign transactions" (I assume one "combined" to many
"transactions") via the TransactionID (the autonumber).

Besides, if you tried to make a concatenated key out of pieces from four
other tables, what happens to your concatenated key when one of those 4
changes?!

If you want to see the pieces of all four put together (for display on a
form or report), use a query to concatenate the pieces.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pieter Wijnen]

It will in most cases be wiser to have a primary key consisting of one field
(depending on the app etc)

Pieter

You don't need to weld the four parts into a separate field, you
can actually create a primary key that includes up to 10 separate
fields.

To do this, create the table with the four fields, then click on
hte indexes button in the toolbar. A dialog box will appear. Enter
an Index name on the first blank row, you can use PrimaryKey, but
that name isn't mandatory, any name will work, and beside it in
the Field Name column pick the first part of your key. In hte rows
below, leave the index Name field blank, just enter the additional
fields.

Click on the Index Name. Set the Primary property to Yes. close
the dialog and save the table.

I forgot to mention that you simply combine the four parts for
display in forms and report, or in a query. [Part_1] & "-" &
[Part_2] & "-" & [Part_3] & "-" & [Part_4]