Finding duplicate records
-
What is the most efficient method of identifying duplicate records in my database? The duplication is likely to be in more than one field but within the same table. I don't want to enforce non-duplication using a primary key as the duplicates won't neccessarily need to be removed. I thought of building up a list of the unique entries and then comparing each record against this but it seems like it would take an awful long time. Cheers James Spibey Well cover me in honey and throw me to the lesbians!
-
What is the most efficient method of identifying duplicate records in my database? The duplication is likely to be in more than one field but within the same table. I don't want to enforce non-duplication using a primary key as the duplicates won't neccessarily need to be removed. I thought of building up a list of the unique entries and then comparing each record against this but it seems like it would take an awful long time. Cheers James Spibey Well cover me in honey and throw me to the lesbians!
Hi Imagine you have a table with 2 fields (name and age): 1. To find duplicate names, use SELECT [name], COUNT(*) AS TOTAL FROM [table] GROUP BY [name] HAVING COUNT(*)>1 2. If you need to check both fields, just add [age] in SELECT and GROUP BY. HTH Regards, Wanderley ps: COUNT(*) will give you the number of times your field is duplicated
-
Hi Imagine you have a table with 2 fields (name and age): 1. To find duplicate names, use SELECT [name], COUNT(*) AS TOTAL FROM [table] GROUP BY [name] HAVING COUNT(*)>1 2. If you need to check both fields, just add [age] in SELECT and GROUP BY. HTH Regards, Wanderley ps: COUNT(*) will give you the number of times your field is duplicated
Thanks for your response - it's a great help. How would I adjust the query to print out the duplicate records in grouped by the duplicate names ie
Name Age Shoe Size
Tom 12 6
Tom 12 7
Tom 12 8
Steve 26 12
Steve 26 13(Excessive amount of sample data, I know) Cheers James Spibey Well cover me in honey and throw me to the lesbians!
-
Thanks for your response - it's a great help. How would I adjust the query to print out the duplicate records in grouped by the duplicate names ie
Name Age Shoe Size
Tom 12 6
Tom 12 7
Tom 12 8
Steve 26 12
Steve 26 13(Excessive amount of sample data, I know) Cheers James Spibey Well cover me in honey and throw me to the lesbians!
Hi Do you want just a list of unique names or you need to know the duplicated ones? From your example:
Name Age Shoe Size
Tom 12 6
Tom 12 7
Tom 12 8
Steve 26 12
Steve 26 13
John 12 9Do you want Tom, Steve and John (without duplicates) or Tom and Steve? Regards, Wanderley
-
Hi Do you want just a list of unique names or you need to know the duplicated ones? From your example:
Name Age Shoe Size
Tom 12 6
Tom 12 7
Tom 12 8
Steve 26 12
Steve 26 13
John 12 9Do you want Tom, Steve and John (without duplicates) or Tom and Steve? Regards, Wanderley
Hi Wanderley - thanks for the help so far, I want to find all the records which are duplicated. Those which are duplicated, I want to be able to list each of the duplicated records giving the user the option to pick which one is the correct one. All the records for which there is only one entry should not be displayed. Does this make sense? So I want to display the information as displayed above. The database would probably also contain many records which were not duplicated which have not been displayed. Can this be done? James Spibey Well cover me in honey and throw me to the lesbians!
-
Hi Wanderley - thanks for the help so far, I want to find all the records which are duplicated. Those which are duplicated, I want to be able to list each of the duplicated records giving the user the option to pick which one is the correct one. All the records for which there is only one entry should not be displayed. Does this make sense? So I want to display the information as displayed above. The database would probably also contain many records which were not duplicated which have not been displayed. Can this be done? James Spibey Well cover me in honey and throw me to the lesbians!
Hi Assuming that you're checking for duplicated names, you could do something like this: 1. Create a combobox and fill it with all duplicated names using SELECT [Name] FROM [your table] GROUP BY [Name] HAVING COUNT(*)>1 2. Create a listbox (or listview) and fill it with all records that match with the name selected on combobox using SELECT * FROM [your table] WHERE [Name] = 'selected name on combo' This way, you'll have all duplicated names on the combobox - whenever you select a name, you can clear and fill the listbox and select the correct one. Is it too confusing? :) Please let me know if you still have problems. Regards, Wanderley
-
Hi Assuming that you're checking for duplicated names, you could do something like this: 1. Create a combobox and fill it with all duplicated names using SELECT [Name] FROM [your table] GROUP BY [Name] HAVING COUNT(*)>1 2. Create a listbox (or listview) and fill it with all records that match with the name selected on combobox using SELECT * FROM [your table] WHERE [Name] = 'selected name on combo' This way, you'll have all duplicated names on the combobox - whenever you select a name, you can clear and fill the listbox and select the correct one. Is it too confusing? :) Please let me know if you still have problems. Regards, Wanderley
Thanks again Wanderley. I understand what you said and it's pretty much what I thought in the first place. Thanks for your help. James Spibey Well cover me in honey and throw me to the lesbians!