Remove Duplicates - NEW TWIST 1

[gregas]

My apologies for not being more clear the first time -- I figure this will change the responses this 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 (nor can I change the existing design). Customers enter data on a form, along with some cc info. Only the Customer data is stored in the Customer table. 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

 

[JoeMiller]

Try this web page:

http://www.fabalou.com/Access/Queries/delete_dupe_records.htm

HTH Joe Miller
joe.miller@flotech.net

 

[gregas]

Excellent, thanks Joe -- that definitely works! It actually turned out to be a little nastier with some other table dependencies (db integrity) and such. I implemented a similar solution with multiple queries and a macro.

Again, gracias!
-aaron

 

[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

 

[Quehay]

If you're just tossing out the child records that are linked to duplicate fKeys then you should be able to just set Cascading Deletes on and get rid of the duplicate parent & child records in one fell swoop with the original strategem.

Also, apart from the form entry, if you create an autonumber TransactID this would be unique and created on DBMS side without Web glitches--maybe a TransTable where the customers (Access won't allow autonumber to be used w/out making it the Primary Key I think...) are joined with Orders. (But you said you can't change this stuff...it's much better to inherit money, no?)