Remove Duplicates Query

[gregas]

I need to build a query that removes the exact duplicates from a table. Note: this is not a one-time deal -- I want to create a query that can be used periodically (daily) -- so I don't want to implement a solution like the one described in Microsoft's knowledge base (Q209183).

Any help would be greatly appreciated.

 

[raskew]

You could use a modified version of the query created by the Find Duplicates wizard, but to describe an exact technique we'd need to know more about your table.

The question that comes to mind is: What is it about your table design that allows duplicate values to be entered on a daily basis?

 

[gregas]

Thanks for responding.

My apologies for not being more clear the first time.

The table in question is a Customer table that tracks Customers as they enter data from a Web form. I did not design the table nor the database, but the way it's set up currently -- Customers enter data on a form, along with some cc info. However, the application (cc verification, esp) has been know to throw errors when processing the form -- but not without the database firing the update. In short, multiple records (WITH DIFFERENT NON-DISTINCT CustomerIDs) can easily appear in the Customer table for a single cc transation.

The task is to create a query/macro which can be used often to clean out the customer table and return only distinct customer records.

Thanks in advance for the help.

-aaron

 

[tlbroadbent]

You can write a simple macro that will delete duplicates. It runs three queries and performs cleanup by deleting a temporary table created by one of the queries. System warning messages are suppressed to allow the macro to run unattended.

Action Argument
SetWarningsOn No
RunSQL SELECT DISTINCT Members.* INTO TempTbl FROM Members;
RunSQL Delete * From Members;
RunSQL INSERT INTO Members SELECT TempTbl.* FROM TempTbl;
DeleteObject Table;TempTbl
SetWarningsOn Yes

Hope this is helpful. Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[tlbroadbent]

Aaron,

Have you reviewed the solution I posted in your earlier thread? Does it do what you need? If not, let me know the remaining issues.

Thanks, Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[gregas]

Terry,

The big issue I'm having is that I need to preserve the CustomerIDs from the records that I do keep. As there are NO distinct records in the Customer table to begin with, I simply cannot select distinct records. If I run a long SQL statement with all the fields (except CustID, of course) in a GROUP BY statement, the statement becomes too long to implement in a macro. Hence, I seem to headed down the Module path -- with strSQL statements being executed to open recordsets.

Any thoughts on this approach?

Thanks for the help,
aaron