Concatenate Fields to Create Primary Key

[kathleen]

I am setting up a table to maintain Bill of Materials. I am in a headquarters environment so I am setting it up for multiple entities. There is a possibility that I can have the same Raw Material being used at multiple sites however the price maybe different at each site. Therefore I would like my primary key to be a combination of Site Designation and Part Number. How do I define a key which will concatentate the two fields. Your help is appreciated.

 

[Norie]

Why not use Autonumber as the primary key and have fields for the Site Designation and Part Number?

If you need to later on in a query you can easily concatenate these two fields.

They could also be used to say get the max price for a part across sites, create a list of parts/prices for a site etc

 

[kathleen]

Thought about that but.....

I want to ensure that the same Part Number is not added for the same site, the creation of the composite key would allow me to ensure that I have a distinct value.

Thanks for the thought!

 

[Andrew Fergus]

Hi,
have you considered having a dual primary key where both the site and the material are set up in your table as the primary key? This will prevent a material being set up at the same site more than once.
HTH, Andrew.

 

[kathleen]

Andrew, thats exactly what I want! How do I do this? Thank you

 

[Andrew Fergus]

Hi,
open the table in design view, click on "site designation" (I think that is your variable name) so that it is highlighted, hold down the control (Ctrl) key and then click the "part number" so that both fields are now highlighted, release the control key, Click Edit -> Primary Key. Save. Hey presto, you have a dual primary key that won't allow duplicate parts per site.
HTH, Andrew.

 

[kathleen]

Thank you! That works like a charm.