If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
3 Field Primary Key
I create two tables.
Table: RFQNo Fields: RFQNo - PK, RFQType Table: LineItems Fields: RFQNo, ChangeNo, LineItemNo, (these 3 combined equal the PK for this table and is the only way to get a unique combination, while RFQNo is also a FK) There are also other fields. I don't think this is correct because I put together a form (RFQNo) with a sub-form (LineItems) and keep getting a message about duplicate values and PK's whenever I click on the subform. I'm confused. Please help! |
#2
|
|||
|
|||
3 Field Primary Key
Hi Natette,
There is nothing wrong with the design as you describe it; compond keys of more than one element are quite common and to specify such a key to Access just select all the element lines in Table Design and click on the primary key button on the toolbar. View/Indexes will show you what Access has done. However ... I personally avoid compound keys although they can be useful - more of that in a moment. You can avoid compound keys by introducing an arbitrary primary key whose purpose is simply to identify the record, no more and no less, and has no meaning 'in the outside world.' Autonumber is ideal for this. Simply add a new field in the table design called, say LinePK, assign it to the data type Autonumber and make it the primary key. I would recommend you specify that RFQNo, ChangeNo and LineItemNo are all indexed with duplicates allowed. (BTW I suspect a better name for LineItemNo is LineNo.) Why can compound keys be useful? Well when dealing with Recordsets it is easier to find a specific unique row from the data values that the user knows (RFQNo etc.). Hey but if you have this requirement there is nothing to stop you specifying a non primary compound key. Use the View/Indexes dialog box for doing this. Hope this helps. Rod "Nanette" wrote: I create two tables. Table: RFQNo Fields: RFQNo - PK, RFQType Table: LineItems Fields: RFQNo, ChangeNo, LineItemNo, (these 3 combined equal the PK for this table and is the only way to get a unique combination, while RFQNo is also a FK) There are also other fields. I don't think this is correct because I put together a form (RFQNo) with a sub-form (LineItems) and keep getting a message about duplicate values and PK's whenever I click on the subform. I'm confused. Please help! |
Thread Tools | |
Display Modes | |
|
|