Primary Keys

[KsFireworksGal]

How can I make a primary key out of more than one field in Access 2000 - is
there a way to code it using SQL?

 

[Douglas J. Steele]

Take a look at
http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx

(although its sample

ALTER TABLE tblCustomers
ADD CONSTRAINT CustomerNames UNIQUE
([Last Name], [First Name])

is a terrible example: names rarely make good indexes!)

 

[ksfireworksgal]

Thank you for the information it gives me a good place to start. I am using
products that don't always have a bar code and some of the names are the same
but refer to different items we were using an item number based on the last
five of the bar codes, but not everything has a barcode, so I am trying to
figure out a better primary key than the barcode or item number and I thought
if I combinded the name with an item number maybe that would work.

Take a look at
http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx

(although its sample

ALTER TABLE tblCustomers
ADD CONSTRAINT CustomerNames UNIQUE
([Last Name], [First Name])

is a terrible example: names rarely make good indexes!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How can I make a primary key out of more than one field in Access 2000 -
is
there a way to code it using SQL?

 

[John W. Vinson]

Thank you for the information it gives me a good place to start. I am using
products that don't always have a bar code and some of the names are the same
but refer to different items we were using an item number based on the last
five of the bar codes, but not everything has a barcode, so I am trying to
figure out a better primary key than the barcode or item number and I thought
if I combinded the name with an item number maybe that would work.

You have an item named "Super Widget" with no bar code.
You have a different item also named "Super Widget" with no bar code.

How would YOU tell which is which?

You really, really need some sort of unique identifier. Concatenating two
uncontrolled, nonunique identifiers - one of which may be missing - will not
help you construct a unique identifier! You may want to consider manually
assigning your own item number, or using an autonumber, or something; but the
resulting identifier needs to be reliably unique, non null, and preferably
stable (i.e. you won't refer to the "Super Widget" as S1251 one day and as
K31190 the next).