Creating Combined Primary Key

K

Kevin

I would like to know if it is possible to create a primary key by combining
multiple fields of a record. I need to make sure that a report is not
imported into the master table multiple times.
 
A

Arvin Meyer

What you want is a compound index. It does not have to be the primary key to
make it work as you wish. To set it as the primary key, move both fields
together in the table design view, select them both and click on the Key
icon on the toolbar.

To set a regular compound index, open the Index dialog and type in an index
name (I usually call it CompoundUnique so I know exactly what it's for) the
add up to 10 fields in the fields column.

One note of interest. You can only have one null value per compound indexed
field in any table.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
D

Dave M

In table design view, menu View | Indexes, then enter a suitable name in the
Index Name column, select the three fields you want in the Field Name column
and then click the index name again to set the Unique property to Yes.
 
J

John Vinson

I would like to know if it is possible to create a primary key by combining
multiple fields of a record. I need to make sure that a report is not
imported into the master table multiple times.
Click to expand...

Dave's and Arvin's methods will work; an alternative is to open the
table in design view, and ctrl-mouseclick on all of the fields which
should jointly constitute the Primary Key. If you then click on the
"key" icon, Access will create a unique primary-key index on all of
the fields (up to ten are allowed).

It's neither necessary nor a good idea to create a redundant composite
field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top