Duplicate Rows

[k209310]

I have inhertited a database that has been poorly designed. It contains a table thant has many duplicate rows (too many to delete by hand) IS there an easier way to delete duplicate values other than to scroll through thousand of entries and delete them by hand?

 

[ColinEssex]

One way I did this was to create a MakeTable query. Put all the fields you need into the query including a "Count" field. Put "GroupBy" in all the other fields. What will happen is that the query will put one row of data (plus the count) into the new table. You then need to delete the count column. Empty the original table and append the rows from the new table back into it.

You then have the old table with one example of each row.

Good Luck
Col

 

[k209310]

Thanks for the reply colin. I get what you mean by append.

However i have two quetions.
1. Where exactly do i put GroupBy in the query? In the criteria collum? Or do i need to put it in to the SQL?

2. What exactly is the count field? Do i add this as a field in to my table? If so exaclty whatt do i do with it?

(i know technically i have asked more than two questions here. I appreciate your reply.

Chris

 

[UKBobby]

I'm not sure why Colin suggested adding a Count field but the GroupBy should work without one - You need to turn your "Totals" on when dealing in query design mode (you know the area you can select where, count, avg etc)

I guess Colin was suggesting the Count Field to prove there were duplicates that have been merged together.

 

[ColinEssex]

When you create the make table query in the design grid, click on the funny E on the toolbar. This will put the "groupBy" in the grid for you.

If you've got a field like ID or something put that in the grid again and change the "GroupBy" for that field to "Count" - that'll be the field you'll do the count on.

Then run the MakeTable query etc etc

Col

 

[ColinEssex]

Just read your post Bobby - you are right on both counts

There is no real need for it but I personally like to see how many duplicates occur.

Thanks for your comment.

Col

 

[k209310]

Thanks to colin and Bobby that has done the trick thanks.

Also i have just been looking at the properties for the query and there is an option to slelect unique record. If this is set to YES then the DistinctRow SQL statement appear in the SQL. Does this do the same job as what colin has described?

Thanks again for the help.