How to define composite keys in MS Access?

Question

I am new to MS Access. Could anybody tell me how to define a composite primary key in MS Access 2007.

Divulsion

Answer 1

If memory serves correctly, you can hold SHIFT while selecting the fields in the design view. Once you've selected all of the fields that are to be part of the key, press the primary key button.

Contemporize

Shift, not Control. Alternatively, you can select them in the indexes property sheet, with the index name in the first column, and nothing in the first column for the subsequent fields of the composite index. – Conventioner

Answer 2

Already answered, but I wanted to chime in with additional info.

After solving my related problem, holding "ctrl" + clicking multiple columns in design view and then clicking the "primary key" icon in the upper left worked great.

I was receiving an error that stated,

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship.

I wasn't sure if it was my data, or if I was doing something wrong. Sure enough it was my data - I had accidentally entered duplicate data without realizing it.

I used the following query that I skimmed off of a dbforums.com post to quickly identify the problem:

SELECT ColumnName1
     , ColumnName2
     , ColumnName3
     , ColumnName4
FROM   TableName 
GROUP
    BY ColumnName1
     , ColumnName2
     , ColumnName3
     , ColumnName4
HAVING Count(*) > 1

After adapting the SQL to my tables the query immediately pointed out the offending entry. I eliminated it, fixed the issue in my table that allowed the duplicate to be created, and was able to create my composite primary key no problem.

Albin