3 Field Primary Key

[Nanette]

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!

 

[Rod Plastow]

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