remove dupes qry

[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.

 

[tlbroadbent]

First, I would examine the database and application design. Relational databases shouldn't have duplicate records - at least not by design. I

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.
[tt]
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[/tt]

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

 

[Cagliostro]

What do you think about making all the colums as primary key? In this case you will avoid ducplicates. John Fill

ivfmd@mail.md

 

[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 (i.e. I cannot change the current design of the table), 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 known 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

 

[Cagliostro]

Give please a sample of what is wrong and what do you want to get. John Fill

ivfmd@mail.md

 

[gregas]

I am pleased to announce that the issue is solved! To all that responded -- thank you! This was my first post to Tek-Tips and I will definitely use this in the future.

Due to some referential integrity, deleting the duplicate records from Customer was NOT enough! The final solution required the following steps/subqueries -- all of which were called from a form with one button on it:

A. Backup tblCustomer.
B. Backup tblOrder.
C. Select distinct Customer records into tblCustomerTemp.
D. Copy all tblOrder records to tblOrderTemp.
E. Delete all records from tblOrder.
F. Delete all records from tblCustomer.
G. Append tblCustomerTemp to tblCustomer.
H. Insert all Order records from tblTempOrder where CustomerIDs from tblTempOrder and tblCustomer are equal.
I. Delete all records from tblTempCustomer.
J. Delete all records from tblTempOrder.

So, I suppose if one ever needed to delete duplicate records from a table and preserve the integrity with another table -- this is one viable approach.

-aaron