What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
To fix duplicate records in an Access query, first find out whether the source table contains duplicate data or whether the query is showing repeated-looking rows because of its selected fields or joins. Use Access’s Find Duplicates Query Wizard to identify records by the fields that define a true duplicate; inspect joins before hiding repeated output, and back up the database before deleting anything.
First identify what “duplicate” means for your data
A duplicate is not simply two rows that look alike on screen. Decide which field or combination of fields identifies the same real-world record for your task. Two people can share a name, for example, and two transactions can share a date without being duplicates. Microsoft’s Find duplicate records with a query guidance lets you choose the fields used for the comparison.
As an Amazon Associate I earn from qualifying purchases.
There are two main cases: duplicate values are stored in a table, or the query produces multiple rows because of its selected columns or joins. The right fix depends on which case you have.
Recommended Free Tools
Find duplicates in a table or query
- In Access, select Create > Query Wizard.
- Choose Find Duplicates Query Wizard and select the table or query to examine.
- Select the field or fields whose combined values define a duplicate. Use a composite match, such as customer plus transaction date, when one field alone is not a reliable identifier.
- Choose any additional fields you want displayed to help inspect the results, then run the query.
- Review the returned records and determine which, if any, should be retained or corrected.
Microsoft lists this wizard for Microsoft 365 Access and Access 2016, 2019, 2021, and 2024. Ribbon wording can vary by localized installation.
#1 Best Overall
When a SELECT query shows repeated-looking rows
In Access SQL, DISTINCT makes rows unique based on the values in all selected fields. If you select CustomerName and OrderDate, the same customer can appear on multiple rows when the dates differ; those field combinations are distinct. Use DISTINCT when the desired result is a list of unique combinations of selected values, not as a way to repair the underlying data.
SELECT DISTINCT [FieldA], [FieldB]
FROM [YourTable];Replace the example names with your actual table and fields, and select only the values that should define a unique output row. Microsoft documents the behavior of ALL, DISTINCT, and DISTINCTROW predicates and the corresponding Access SQL predicates.
Rank #2
DISTINCTROW is different: it can return unique underlying records in certain joined-query cases. It is not a universal substitute for DISTINCT; Access ignores it for a single-table query and when output fields come from all tables.
Check joins if rows multiply after combining tables
A join returns matching records from its source tables. If one record on one side matches several records on the other, the result can correctly contain several rows for that one record. For instance, one customer joined to several orders produces multiple customer-order rows. That is not necessarily duplicate data.
Rank #3
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
- Inspect the fields used in the join and confirm that they represent the intended relationship.
- Check the relationship’s cardinality: a one-to-many relationship naturally returns multiple matches on the many side.
- Review whether Access created a join based on an existing relationship or compatible fields, and choose a join type that fits the question.
- If you need one row per parent record, select parent fields deliberately or use an appropriate unique-record query design rather than suppressing meaningful child records.
Microsoft explains query joins in its Join tables and queries guide and describes performing joins with Access SQL.
Prevent duplicate values from being entered again
If a field must contain unique values, apply a unique index. If uniqueness depends on multiple fields, define the constraint on the combination that represents the real key, rather than on a convenient field that can legitimately repeat.
Rank #4
Resolve existing duplicates before saving the index. Microsoft notes that Access can reject a unique index when duplicate values are already present, with error 3022. See Prevent duplicate values in a table field using an index for the documented approach.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Delete records only after reviewing and backing up
Finding duplicates and deleting them are separate tasks. Before running a delete query, make a backup, verify that you are working in the intended database, consider whether other users are working in the file, and decide unambiguously which row to retain. Microsoft warns that query deletions cannot be undone. Its procedure applies to desktop databases, not Access web apps; see Delete duplicate records with a query.
Best Value
Compare possible duplicates across tables
For records spread across multiple tables, Microsoft recommends a union query as a way to locate duplicate rows. The corresponding output columns must align in order and meaning. In Access SQL, UNION removes exact duplicate result rows, while UNION ALL retains repeated rows.
A union compares rows; it does not determine whether two records represent the same entity or which source is authoritative. Make that decision using the data’s meaning and the fields that identify a record. See Microsoft’s duplicate-query guidance and Access SQL reference for joins.
If expected matches do not appear
Check field types and imported values before concluding that matching records are absent. Similar-looking data can be stored differently—for example, numbers may be imported as text—so fields with different types may not compare as expected. Standardize the values or compare compatible fields. Microsoft describes comparing tables and finding matching data in Compare two tables in Access and find only matching data.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
