is there a way to remove duplicates but leave one record?

Dear @Mark Powell

I understand your concern. After you locate the duplicate records, you can either edit them manually or use a query to remove the duplicates. Microsoft provides guidance on this here: Find duplicate records with a query and Delete duplicate records with a query

However, a standard Delete Query following Microsoft Support's basic instructions might delete all copies of the matching records, leaving you with zero records.

In this case, according to my research, here are some alternative approaches you can look through:

• How to remove duplicate records but keep one in Microsoft Access? (Disclaimer: This link is shared by community members for your convenience. It points to a third-party site that is not managed or verified by Microsoft. We can’t guarantee the quality, safety, or suitability of any content or software found there. Please review carefully and make sure you understand any potential risks before using it).
• Delete Duplicates (leave 1)

Otherwise, you can use an Append Query. Here are some recommended steps I suggest you try. For reference: Delete Duplicated Records - But Keep 1 (Disclaimer: This link is shared by community members for your convenience. It points to a third-party site that is not managed or verified by Microsoft. We can’t guarantee the quality, safety, or suitability of any content or software found there. Please review carefully and make sure you understand any potential risks before using it).

1/ Make a backup copy of your database > Right-click on your table from the Nav Pane and select Copy > Right-click again on the Nav Pane and select Paste

2/ On the Paste Table As dialog box, select Structure Only and click OK > Open the new empty table in Design View

3/ Open the Indexes window by clicking on the Indexes button on the Ribbon > Create a new index as follows:

• In the Index column, enter Dup
• In the Unique property, change No to Yes
• In the Field Name column select Customer Account Number from the dropdown
• In the row below Customer Account Number, select Appointment Date and Time from the dropdown

4/ Save the table's new design > Create an APPEND query to add the records from the old table to the new table > Run the query, click Yes twice

5/ The new table should now have only unique records. You can either replace the old table with the new one or use it to delete the duplicates from the other table.

Note: Most importantly, remember to back up your database. The only way to recover deleted records is to restore them from a backup. A delete operation might also delete records in related tables.

Moreover, I agree that being able to automatically remove duplicate records while retaining the most recent or preferred record would be a valuable feature and could simplify this process considerably, especially for users who regularly import data into Access.

Therefore, I highly encourage you to submit feedback directly to Microsoft through the Feedback Portal: https://feedbackportal.microsoft.com/ > Feedback submitted there is reviewed by the product team and helps inform future enhancements to Microsoft Access.

I hope the information shared is helpful. If you have any questions or if I have misunderstood anything, please feel free to reach out. I'm looking forward to your update.