2 Primary Keys in 1 table

[JIMR]

Hi all,

Using access 2003 on XP Pro Desktop

I am curious if the following is possible?
I have a table with 2 Primary Keys:

tbl_Inventory
CompanyPartID (PK) (autonumber)
3rdPartyPartID (PK?) (part number provided by 3rd party vendor, no longer here)
Part Description
etc.

I have an Issue Items table:
IssueItemsID (PK)
CompanyPartID (FK)
3rdPartyPartID (FK?)

I would like to create an issue items form that will allow me to use the CompanyPartID (FK) or the 3rdPartyPartID (FK) to scan the part into and populate the other with its appropriate information ex. scan Company Part# barcode into the CompanyPartID (FK) of the issue items form and have it populate the 3rdPartyPartID with its appropriate information or scan the 3rdPartyPartID (FK) and have it populate the appropriate information into the CompanyPartID (FK). To clarify: the appropriate information is the information that the field is populated with in its original table.

I have played around with this quite a bit today and I have been unsuccessful. I am only attempting this because management (regardless of what I suggest) insists that they do not want to use the manpower to relabel all of the (500+) items in our stock room. If I can get this to work we would only have to relabel new additions and of course there would be 2 different numbering schemes that would have to be contended with.

My preference is to use the number (CompanyPartID) as the primary (only 1 Primary key) and relabel all stocked items.

Any thoughts or suggestions would be helpful.

Thanks

 

[boblarson]

You can only have ONE PRIMARY KEY in a table. It can be a single field or a composite key out of two or more fields. However, I would suggest using an autonumber as the primary key and you can have the other information together, but as CompanyPartID or 3rdPartyPartID could potentially change rules, or have duplicates at some point due to a change in vendor, etc., let the system generate the keys and you can index the CompanyPartID as Indexed (No Duplicates) so that you can't enter it more than once.

 

[JIMR]

Thanks boblarson,

Your suggestion of using 1 primary key (autonumber) is what I have but was adding a second in an attempt to allow either of the primary keys to populate (retrieve field information may be what I should say) the other appropriately in a form.

I will just tell management that all items must be relabeled.

Just one question..If there can only be one primary key why does Access allow more than one?

Thanks

 

[Rabbie]

Just one question..If there can only be one primary key why does Access allow more than one?

Thanks

Access doesn't allow more than 1 primary key. What it allows is a composite Primary key made up of two or more fields. I believe the max number is 10.

 

[boblarson]

Just one question..If there can only be one primary key why does Access allow more than one?

I think you're confused with Primary Key versus Composite Primary Key.

A single primary key is when you have a single field selected as a primary key. However, you can have a composite key which you can set by selecting more than one field and then clicking on the Key icon on the tool bar. This creates a composite key which means that you can only have one item which matches the unique combination of fields. So, for example if you had 3 fields as a composite key (ShipDate, ItemNumber, Shipper) that would mean that you could have more than one record for a particular ship date, more than one for a particular item number, and more than one for a particular shipper, BUT you could not have more than one record with the SAME ShipDate, ItemNumber, and Shipper combination.

So, Access only lets you have ONE primary key PER TABLE, but you can (although unless absolutely necessary I would avoid them) have composite keys.

 

[JIMR]

Got it, thanks for explaining.