Removing Duplicate Records From A Table - please h

Results 1 to 3 of 3

Thread: Removing Duplicate Records From A Table - please h

  1. #1
    Join Date
    Dec 1969

    Default Removing Duplicate Records From A Table - please h

    I would like to remove all but the latest (determined by input date) record for each customer in a table and insert these records into an archive table.<BR>ie. I have a table of orders on the web and I need to keep one order for each customer so that I can duplicate their details when entering a new order. As each order is removed I then want to insert it into an archive table.<BR>My main problem at the moment is how to get list of the duplicates.<BR>Please help.......<BR>

  2. #2
    Join Date
    Dec 1969

    Default GROUP BY all your key fields...

    SELECT field1, field2, field3, field4, count(field1)<BR> FROM yourtable<BR> GROUP BY field1, field2, field3, field4<BR> HAVING count(field1) &#062; 1;<BR><BR>Pete

  3. #3
    Join Date
    Dec 1969

    Default Here's what I do

    1. Run a query against your table that puts all of the duplicate<BR> records together by using the ORDER BY clause on the desired<BR> columns.<BR>2. Declare Cur variables and Last variables for EACH column<BR> needed in order to identify the record. In most cases,<BR> this will probably only be one column. For instance,<BR> CustomerName...<BR>3. Declare a variable called sDeleteString. This string <BR> variable will contain a comma delimited string of database<BR> primary keys found that need to be deleted.<BR>4. As you iterate through the recordset, you keep a set of<BR> Cur variables and Last variables. These variables<BR> are populated with the current record&#039;s values).<BR>5. On record #1, set Cur variables = Last variables.<BR>6. On all subsequent records, compare the Cur variables with<BR> the Last record. If they are identical, add the primary key<BR> value to sDeleteString and append another comma to the end<BR> of the string. If they are different, populate the Cur <BR> variables into the Last variables and move onto the next<BR> record.<BR>7. If there were any duplicates found, trim off the last <BR> comma from sDeleteString: <BR> <BR> sDeleteString = Mid(sDeleteString,1,Len(sDeleteString) -1)<BR>8. Delete the duplicate records:<BR> oConn.Execute "delete from table where mykey in (" & sDeleteString & ")" <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts