Fix Find Duplicates Query Wizard Errors in Microsoft Access
Question details
Fix errors in the Access Find Duplicates Query Wizard when duplicate records are visible but the wizard works only in a new database.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- The user wants to find duplicate records in a table in an existing Access database.
- Observed behavior
- The wizard reports “Record is Deleted” and “Item not found in this collection” in the original database, while the same table works in a new database.
Ask other users to close the database and make a copy of the database file before repairing or changing it. Note the affected table and field names so you can test the same data after repair.
Compact and repair the original Access database
Try this first when the same table works in a new database, which suggests a problem with the original file.
The original database may be corrupted, although these errors do not prove corruption. Compact and Repair can resolve some database file issues.
In Access, open the affected database after other users have closed it.
Select File > Info > Compact & Repair Database and wait for Access to finish.
Select Create > Query Wizard > Find Duplicates Query Wizard, choose the affected table and fields, and check whether the query runs.

Import database objects into a new Access file
Use a new database if Compact and Repair does not resolve the wizard errors.
Find duplicate Tax ID records with an Access SQL query
Use SQL as a workaround when the wizard fails but the table can still be queried.
Review exported duplicate records in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface and support for common Office formats, making it easy to move exported data into a spreadsheet. It does not repair Access databases or replace the Access query wizard.

Frequently Asked Questions
Why does the wizard work in a new database but not the original?
The original database may have a file or object problem that is absent from the new database. Try File > Info > Compact & Repair Database, then test the wizard again.
Can I list only the duplicate Tax ID values?
In Create > Query Design > SQL View, enter: SELECT TaxID FROM Customers WHERE TaxID IS NOT NULL GROUP BY TaxID HAVING COUNT(*) > 1; Replace the table and field names as needed.
Does the SQL workaround include blank Tax IDs?
The sample query excludes null TaxID values. If you also need to review missing IDs, run a separate query with WHERE TaxID IS NULL.
Should I add a unique index before removing duplicates?
No. Review and correct duplicate values first, then use the table's Design View to apply an appropriate primary key or unique index.
Can WPS Office repair the Access database?
No. Use Access Compact and Repair or import the objects into a new Access database. WPS Spreadsheets can help you review records exported to an Excel file.




