Copy your table (always step 1 of manipulating data)
Find the ID of the duplicate with the lowest ID
Delete the others
Verify you no longer have duplicates
How what? I gave you the broad strokes. Start down the path and when you have a specific question post back what you tried and we can get through it.
Hi. What constitute a duplicate? Which fields?I have my duplication query showing all the records within duplicated thats as fair as i have got, reads that someone says create a table and then use that table to duplicate in master table, but it deletes all records and doesn't leave 1 record?
Hi. What constitute a duplicate? Which fields?
Okay, try the following steps:Customer account number and appointment date and time when all same
Okay, try the following steps:
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.
- 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
- On the Paste Table As dialog box, select Structure Only and click OK
- Open the new empty table in Design View
- 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
- 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
Hope that helps...
Yes, that's expected. That's why I told you to click Yes twice in step #10. Did you do that? Clicked twice on Yes, both times? Just checking...Ok did thats and got an error message saying Cant append all the records in the append query due to key violations so thats correct right as these ones are the duplicates?
Hi. You tell us. You said what constitute a duplicate is if the customer number and appointment dates and times are the same. So, that's what you identified in the unique index, correct? If so, the new table should only have records without the same customer number and appointment dates and times. You can have duplicate customers or duplicate appointment dates and times, but not both the same. What did you get instead?OK so its seems to have delete all duplicated records using account number, some of the records are the same customer but with different appointment dates and times, so wanted to keep them - did on test data so no big deal did i do something wrong?
Hi. You tell us. You said what constitute a duplicate is if the customer number and appointment dates and times are the same. So, that's what you identified in the unique index, correct? If so, the new table should only have records without the same customer number and appointment dates and times. You can have duplicate customers or duplicate appointment dates and times, but not both the same. What did you get instead?
Sounds like you may have missed step #7.4 - Can you show us a screenshot of the Indexes window?ok so just it removed all records using the account number even if the same customer had 3 appointment dates and times
Sounds like you may have missed step #7.4 - Can you show us a screenshot of the Indexes window?
Hi. That's correct. Should be easy enough to test. Cheers!You were correct i did, all working now, so now i have the new table set with the Indexing - will this stop in future any duplicates being added? for same customer same appointment date and time, but will allow a different date and time for customers