Is it possible to have a primary key based on two fields? I'd like to have the fields "Item Number" and "Production Line" used together because we have some item numbers that are made one production line at one time and another time may be made on a different production line. This had not previously been the case and the database was created with the understanding that "Item Number" was unique and each item was made on a specific production line. But now we want to make items on possibly more than one production line. It would be ideal to be able to say "this specific item number made on this specific line' is the new key. Is this possible? (As you may have guessed I am a novice; using Access 2007). Thank you.
Multiple, Connected Primary Keys?
- Thread starter Ms Kathy
- Start date
spikepl
Eledittingent Beliped
- Local time
- Today, 14:55
- Joined
- Nov 3, 2010
- Messages
- 6,142
I want to be able to enter an Item Number of "4060" and a Production Line of "Line1" for one record in the table; and be able to enter an Item Number of "4060" and a Production Line of "Line2" for another record in the table.
Selecting "no duplicates" on the Item Number field prohibits me from doing this. But I surely need this field set as a "no duplicates" in order to have a unique identifier don't I?
Selecting "no duplicates" on the Item Number field prohibits me from doing this. But I surely need this field set as a "no duplicates" in order to have a unique identifier don't I?
Meaningful informational help request:
I want this ... to happen,
I want this to happen:
I want to be able to enter an Item Number of "4060" and a Production Line of "Line1" for one record in the table; and be able to enter an Item Number of "4060" and a Production Line of "Line2" for another record in the table.
Selecting "no duplicates" on the Item Number field prohibits me from doing this. But I surely need this field set as a "no duplicates" in order to have a unique identifier don't I?
I want this ... to happen,
I want this to happen:
I want to be able to enter an Item Number of "4060" and a Production Line of "Line1" for one record in the table; and be able to enter an Item Number of "4060" and a Production Line of "Line2" for another record in the table.
Selecting "no duplicates" on the Item Number field prohibits me from doing this. But I surely need this field set as a "no duplicates" in order to have a unique identifier don't I?
- Local time
- Today, 08:55
- Joined
- Feb 19, 2002
- Messages
- 47,572
Access allows you to select up to 10 columns to make up a composite PK or index. As the link said, to select multiple columns, use the cntl key and while holding it, select up to 10 columns. When they are all highlighted, press the key button.
You can also use the indexes dialog and type the fields in if you want to control the field order.
Based on your question though, this is probably not the correct solution for you. I think you probably need to add an additional table so you can create a many-to-many relationship.
Your original relationship was 1-many. Each item could be produced on only one line so the line was kept in the item table. Now that Item can be produced on multiple lines, you need to remove line from the item table and add a new table. The new table is called a junction table and its job is to connect items with lines. Once you do this, an item can be made on any line not just one.
The problem with what you are planning is that it will duplicate item data. So for every line, you will need to enter all the attributes that describe the item and that will be a violation of second normal form. Do some reading on normalization so you understand my advice. I answered your technical question but my advice is - don't use it.
You can also use the indexes dialog and type the fields in if you want to control the field order.
Based on your question though, this is probably not the correct solution for you. I think you probably need to add an additional table so you can create a many-to-many relationship.
Your original relationship was 1-many. Each item could be produced on only one line so the line was kept in the item table. Now that Item can be produced on multiple lines, you need to remove line from the item table and add a new table. The new table is called a junction table and its job is to connect items with lines. Once you do this, an item can be made on any line not just one.
The problem with what you are planning is that it will duplicate item data. So for every line, you will need to enter all the attributes that describe the item and that will be a violation of second normal form. Do some reading on normalization so you understand my advice. I answered your technical question but my advice is - don't use it.
MSAccessRookie
AWF VIP
- Local time
- Today, 08:55
- Joined
- May 2, 2008
- Messages
- 3,428
Does the Primary Key need to be one of your Data Fields? Consider adding a new Field with Type AutoNumber to the Table and making it the Primary Key. (I would try to put it at the beginning, but I do not think it matters). Since your Data Fields are not keys, you would be able to duplicate them as you need. You should still be able to use the Data Fields to link the tables, but you might need to create Relationships if you need any additional indexes. Inb addition, you wil need to verify whether any of the Data Fields involved have "No Duplicates" set.
-- Rookie
-- Rookie
- Local time
- Today, 13:55
- Joined
- Sep 12, 2006
- Messages
- 16,048
let's say you have an orders table, and an ordered items table. (which sounds like it's similar to the problem you have)
now the orders table probably has a single field primary key, OrderNumber. The ordered items table needs a composite primary key, using 2 fields order number, and order line.
now, using a composite primary key can become more fiddly as you need to relate this table to other tables. so INSTEAD OF designating the composite key as the Primary Key, you could add an autonumber field, and make that the PK (for the purposes of relating other tables.
You still retain the composite key as a UNIQUE KEY.
It's a matter of taste as to whether you want to have this extra key. Personally as far as I am concerned, the slight overhead of adding the extra autonumber is more than offset by the ease of using the autonumber, as opposed to having to manage composite keys everywhere
eg.
... "where recordid = " & autonumber, as opposed to
.... "where recordid = " & orderid & " and recordline = " & orderline (maybe needing string formatting as well)
now the orders table probably has a single field primary key, OrderNumber. The ordered items table needs a composite primary key, using 2 fields order number, and order line.
now, using a composite primary key can become more fiddly as you need to relate this table to other tables. so INSTEAD OF designating the composite key as the Primary Key, you could add an autonumber field, and make that the PK (for the purposes of relating other tables.
You still retain the composite key as a UNIQUE KEY.
It's a matter of taste as to whether you want to have this extra key. Personally as far as I am concerned, the slight overhead of adding the extra autonumber is more than offset by the ease of using the autonumber, as opposed to having to manage composite keys everywhere
eg.
... "where recordid = " & autonumber, as opposed to
.... "where recordid = " & orderid & " and recordline = " & orderline (maybe needing string formatting as well)
Thank you for your informative response! I am now trying to wrap my head around the junction tables. I searched on line and have a basic understanding. I came up with the following. I created a new table for ProductionLine1, and a new table for ProductionLine2 (etc.). The primary key for these tables is an auto lookup to the item number in my main table. I have items that can be made on Line1 and/or Line2. The specifics for the fields in the Line1 / Line2 tables are related to the item itself (e.g. bake time, deposit weight). Can I create a junction table that links from one item in the Items table and several production line tables (Line1, Line2)? Any help is greatly appreciated!
