Others

Use an AutoNumber Primary Key and Unique Course Index in Microsoft Access

Anum MushtaqAnum Mushtaq Nov 20, 2026 869 views

Question details

Prevent duplicate combinations of course title, section, course number, and subject while assigning each course a primary key.

Use an AutoNumber Primary Key and Unique Course Index in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
A course table needs a reliable identifier and a constraint that keeps each combination of four course fields unique.
Observed behavior
A calculated field cannot serve as the table's primary key.
Before you start

Identify the four stored fields that define a unique course and check the table for existing duplicate combinations. Resolve duplicates before creating the unique index, and decide whether all four fields must be required.

Solution 1Recommended

Add a CourseID primary key and a unique course index

Use an AutoNumber identifier for relationships and a separate unique index to prevent duplicate course combinations.

1
Open the course table

In the Navigation Pane, right-click the course table and select Design View.

2
Create the primary key

Add a field named CourseID, set its Data Type to AutoNumber, and select Primary Key on the Table Design tab. Save the table.

3
Require the course fields

In Design View, select each of the title, section, course number, and subject fields and set its Required property to Yes if every value must be present.

4
Define the unique index

Select Indexes on the Table Design tab. Enter an index name and the title field on the first row, then add section, course number, and subject on the following rows, leaving their Index Name cells blank. Select the first row and set the Unique property to Yes.

5
Save and link related tables

Close the Indexes dialog and save the table. In related tables, add a Number field with Field Size set to Long Integer for CourseID, then use Database Tools > Relationships to link it to the course table's CourseID.

Add a CourseID primary key and a unique course index
Use stored fields for the index: Create the index from the four stored course fields, not from a calculated field. If Access cannot save the unique index, check for existing duplicate combinations and resolve them first.
Free Microsoft Office alternative

Review exported course lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with Office files. Export course data from Access to Excel, then open it in WPS Spreadsheets for review; WPS does not replace Access database keys or unique indexes.

Review exported course titles, sections, numbers, and subjects in a spreadsheetCompatible with Microsoft Excel XLSX filesFamiliar spreadsheet interface makes it easy to work with exported Office dataFree and lightweight for everyday document and spreadsheet tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use a calculated field as the primary key?

Access does not allow a calculated field to serve as a primary key. Add an AutoNumber field such as CourseID in Design View and select Primary Key for that field instead.

How does the unique index prevent duplicate courses?

In Table Design > Indexes, put the four stored course fields in one index and set Unique to Yes. Access then prevents records with the same indexed combination.

Why won't Access save my unique index?

The table may already contain duplicate combinations. Find and resolve those records, then return to Table Design > Indexes and save the unique index again.

What type should the CourseID field use in a related table?

Use a Number field with Field Size set to Long Integer to match the course table's AutoNumber CourseID. Create the link under Database Tools > Relationships.