Others

Prevent Duplicate Records with a Multi-Field Index in Microsoft Access

Jason MillerJason Miller Nov 20, 2026 869 views

Question details

Prevent more than one KPI record for the same account owner, month, and fiscal year.

Prevent Duplicate Records with a Multi-Field Index in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
A database stores monthly KPI records for account owners and must enforce one record per owner and fiscal period.
Observed behavior
The desired result is a database-level rule that rejects duplicate combinations of account owner, month, and fiscal year.
Before you start

Make sure the KPI table contains AccountOwnerID, KPIMonth, and FiscalYear fields, and check for existing duplicate combinations. Resolve any duplicates before creating the unique index.

Solution 1Recommended

Create a unique multi-field index in the KPI table

Use the Access Indexes window to enforce one KPI record per account owner, month, and fiscal year.

Keep account owners in a separate table and use AccountOwnerID as the relationship field in the KPI table. If each KPI record has multiple scores, store those scores in a separate table linked by KPIID.

Use a field name such as KPIMonth rather than Month, which can conflict with an Access function or reserved word.

1
Open the KPI table

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

2
Require the index fields

Select AccountOwnerID, KPIMonth, and FiscalYear in turn. In each field's General properties, set Required to Yes so every record has a complete combination.

3
Open the Indexes window

On the Table Design tab, select Indexes. Enter an index name such as UniqueOwnerPeriod in a blank row, then select AccountOwnerID in the Field Name column.

4
Add the other fields

In the next two rows, leave Index Name blank and select KPIMonth and FiscalYear in the Field Name column. These rows become part of the same index.

5
Make the index unique and save

Select the named index row, set Unique to Yes in Index Properties, close the Indexes window, and save the table. Try entering a duplicate owner, month, and year to confirm Access rejects it.

Create a unique multi-field index in the KPI table
Existing duplicates: Access cannot save the unique index while duplicate combinations already exist. Correct or remove those records, then save the table again.
Free Microsoft Office alternative

Review KPI data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for reviewing and sharing spreadsheet-based KPI data. It supports common Microsoft Office formats for an easier transition, but it does not create or enforce unique indexes in an Access database.

Review monthly KPI data in a familiar spreadsheet interfaceOpen and edit Microsoft Excel XLSX and XLS filesWork with a free, lightweight office suiteShare spreadsheet-based reports with Microsoft Office users
microsoft office alternative - wps office

Frequently Asked Questions

How do I find duplicate combinations before creating the index?

In Access, create a query that groups by AccountOwnerID, KPIMonth, and FiscalYear and counts records in each group. Review groups with a count greater than one before saving the unique index.

Can the same account owner have records for different months?

Yes. The index rejects only repeated combinations of AccountOwnerID, KPIMonth, and FiscalYear. The same owner can have records for other months or fiscal years.

Where should I store multiple scores for one KPI record?

Store the scores in a separate KPI scores table with a KPIID field that links each score to its parent KPI record. Keep the unique owner, month, and year index on the KPI table.

Why should I make all three fields required?

A complete combination is needed to apply the intended business rule consistently. In the KPI table's Design View, select each field and set Required to Yes in its General properties.