Double Primary Key question

[TeeSee]

Please critique! Let's see if I understand this Primary key thing!
Customer in this code is someone I sell to. Go Figure!
I have a table called tblCustomerItems with three of the fields being
txtCustID, txtMyCode and txtCustItemCode. There could be duplicate
values in each of the three fields ....... therfore (I think) I have
to make a double primary key to make a uneek value. So I believe
txtCustID and txtCustItemCode if "joined" would give a uneek value. If
I have that bit correct .....? .....some of my customers do not have
(or provide) uneek item codes and so since you cannot have a NULL
value in a primary key field, how best do you massage these fields to
allow the primary key?

As always thankful for and appreciate the help and insight!aa

 

[BruceM]

One approach may be to add an autonumber field to serve as the PK. There is
no way to evaluate from here whether your combined fields would represent a
unique value, but I suspect not.

More information about your database structure is needed. Is
tblCustomerItems linked to a Customer table? What real-world situation is
being modeled by tblCustomerItems (i.e. what exactly are you storing)? How
does this information relate to the rest of the database?

 

[John W. Vinson]

Please critique! Let's see if I understand this Primary key thing!
Customer in this code is someone I sell to. Go Figure!
I have a table called tblCustomerItems with three of the fields being
txtCustID, txtMyCode and txtCustItemCode. There could be duplicate
values in each of the three fields ....... therfore (I think) I have
to make a double primary key to make a uneek value. So I believe
txtCustID and txtCustItemCode if "joined" would give a uneek value. If
I have that bit correct .....? .....some of my customers do not have
(or provide) uneek item codes and so since you cannot have a NULL
value in a primary key field, how best do you massage these fields to
allow the primary key?

As always thankful for and appreciate the help and insight!aa

If you just want to ensure that you have one and only one row for each
combination of txtCustID and txtMyCode, just select those two fields in table
design view and click the Key icon to make them a joint two-field primary key.
I would be inclined NOT to put a unique index on txtCustItemCode, since you
have no control over it - different customers might both use 1231 as their
part number for one of your products, but they might be DIFFERENT products!

If this table will be related to other child tables, then do take Bruce's
advice and use an Autonumber primary key; you can then separately create a
unique Index specifying the two fields.

 

[Evi]

You don't need to set a combination of fields as a Primary Key to make them
unique. You can ensure a unique value in a combination of fields by using
the Index button in table design view.
Choose any name you want for your index in the first column. In the second
column, choose the name of your first field.
Put nothing in the first column of the next row but choose the next field in
the 2nd column
Do the same for the other fields.
Click back in the first column on the row that contains the name which you
typed for your index.

In the section below that you will see a box. One row will be headed Unique.
Choose Yes.

Evi