using multiple columns as key?

[Edm]

Hi.

I use access to manage my inventory, which comes in with 4
identifying numbers. The problem is, no one of these
numbers is 100% unique. An idea that I have is to use all
4 numbers to generate a truly unique number for each
unit. Now the question is, is it possible to combine 4
different columns data on the same row and have access
allow duplicates in each individual column, but have all 4
columns in one row not be able to have the same values
when compared to other rows.

Sorry if this sounds confusing.

Edm

 

[Dennis Snelgrove]

Not confusing at all. Yes, it's possible. Ctrl-click on the four rows in the
Table design screen, then click on the Primary key button. At that point,
the four fields can have any and all values they want, repeating or not. The
only thing is that every combination of the four has to be unique. That is,
you could have 100 records where the same 3 fields are identical, but the
fourth has to be different in every record.

 

[Tim Ferguson]

is it possible to combine 4
different columns data on the same row and have access
allow duplicates in each individual column, but have all 4
columns in one row not be able to have the same values
when compared to other rows.

Yes: it's a compound key. There's a quick way and a short way...

1) Ctrl-click each row in the table design and then click the yellow
"primary key" button.

2) Open the Indexes window (View | Indexes) and create a new row. Give the
index a Name in the first column, and pick the options from the bottom half
(primary, unique, ignore_nulls). Then choose the field names in order, in
the samerow and the other three below it. This is the only way to create a
non-primary unique index.

The table will be checked for uniqueness when you try to save changes. If
there are duplicates, you won't be able to create the index until they are
sorted out. Oh- bear in mind that you cannot have any nulls in any PK
fields.

Hope that helps


Tim F

 

[Vojin]

You can use more than one field (column) to define a
primary key.

To do that, go to "table design" mode, and select multiple
fileds by holding the CTRL key.

Once you do that, click on the "primary key" icon on the
toolbar. If the combination of 4 fields is not unique,
Access will give you an error message. If not you are in
business.

Good luck.

V.