Prevent Duplicate Records with a Multi-Field Index in Microsoft Access
Question details
Prevent more than one KPI record for the same account owner, month, and fiscal year.

- 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.
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.
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.
In the Navigation Pane, right-click the KPI table and select Design View.
Select AccountOwnerID, KPIMonth, and FiscalYear in turn. In each field's General properties, set Required to Yes so every record has a complete combination.
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.
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.
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.

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.

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.




