Solved Delete Duplicated Records - But Keep 1 (1 Viewer)

Number11

Member
Local time
Today, 20:53
Joined
Jan 29, 2020
Messages
607
So I need to do some data cleansing and need to find the best way to delete duplicated records but keeping just 1

Is this possible to Run from a query ?
 

plog

Banishment Pending
Local time
Today, 15:53
Joined
May 11, 2011
Messages
11,611
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
 

Number11

Member
Local time
Today, 20:53
Joined
Jan 29, 2020
Messages
607
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

sound simple but how?
 

plog

Banishment Pending
Local time
Today, 15:53
Joined
May 11, 2011
Messages
11,611
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.
 

Number11

Member
Local time
Today, 20:53
Joined
Jan 29, 2020
Messages
607
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.

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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:53
Joined
Oct 29, 2018
Messages
21,358
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:53
Joined
Oct 29, 2018
Messages
21,358
Customer account number and appointment date and time when all same
Okay, try the following steps:
  1. Make a backup copy of your database
  2. Right-click on your table from the Nav Pane and select Copy
  3. Right-click again on the Nav Pane and select Paste
  4. On the Paste Table As dialog box, select Structure Only and click OK
  5. Open the new empty table in Design View
  6. Open the Indexes window by clicking on the Indexes button on the Ribbon
  7. Create a new index as follows:
    1. In the Index column, enter Dup
    2. In the Unique property, change No to Yes
    3. In the Field Name column select Customer Account Number from the dropdown
    4. In the row below Customer Account Number, select Appointment Date and Time from the dropdown
  8. Save the table's new design
  9. Create an APPEND query to add the records from the old table to the new table
  10. Run the query, click Yes twice
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.

Hope that helps...
 

Number11

Member
Local time
Today, 20:53
Joined
Jan 29, 2020
Messages
607
Okay, try the following steps:
  1. Make a backup copy of your database
  2. Right-click on your table from the Nav Pane and select Copy
  3. Right-click again on the Nav Pane and select Paste
  4. On the Paste Table As dialog box, select Structure Only and click OK
  5. Open the new empty table in Design View
  6. Open the Indexes window by clicking on the Indexes button on the Ribbon
  7. Create a new index as follows:
    1. In the Index column, enter Dup
    2. In the Unique property, change No to Yes
    3. In the Field Name column select Customer Account Number from the dropdown
    4. In the row below Customer Account Number, select Appointment Date and Time from the dropdown
  8. Save the table's new design
  9. Create an APPEND query to add the records from the old table to the new table
  10. Run the query, click Yes twice
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.

Hope that helps...

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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:53
Joined
Oct 29, 2018
Messages
21,358
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?
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...
 

Number11

Member
Local time
Today, 20:53
Joined
Jan 29, 2020
Messages
607
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:53
Joined
Oct 29, 2018
Messages
21,358
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?
 

Number11

Member
Local time
Today, 20:53
Joined
Jan 29, 2020
Messages
607
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 just it removed all records using the account number even if the same customer had 3 appointment dates and times

Ok sorted it now i made an error in the index thank you so much :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:53
Joined
Oct 29, 2018
Messages
21,358
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?
 

Number11

Member
Local time
Today, 20:53
Joined
Jan 29, 2020
Messages
607
Sounds like you may have missed step #7.4 - Can you show us a screenshot of the Indexes window?

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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:53
Joined
Oct 29, 2018
Messages
21,358
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
Hi. That's correct. Should be easy enough to test. Cheers!
 

Users who are viewing this thread

Top Bottom