sql statement to remove duplicates (1 Viewer)

[tinyevil777]

Hey there,

I'm trying to remove duplicate records that have the same ID and Total. These records are in one table.

I've just using the following code, however it returns no records.

SELECT First(test_order.OrderID) AS [OrderID Field], First(test_order.Total) AS [Total Field], Count(test_order.OrderID) AS NumberOfDups
FROM test_order
GROUP BY test_order.OrderID, test_order.Total
HAVING (((Count(test_order.OrderID))>1) AND ((Count(test_order.Total))>1));

Any idea where i'm going wrong?

Thank you in advance!

Tom

 

[jdraw]

This will just Find the duplicates.

SELECT OrderID , Count( OrderID)
FROM test_order
GROUP BY OrderID
HAVING Count( OrderID) >1 ;

You will need a delete query to remove them.

 

[gemma-the-husky]

i dont see how you can remove just one (or all but one) of duplicated items directly.

the easiest way would be to create a new blank table structured as you want it, but then add a unique key to intercept the duplicates

now append the existing table into the new table - the duplicates will be rejected.

 

[the_net_2.0]

actually I wrote a function to do just this because I noticed that Access doesn't give you a resource to do it:

Function DeleteDups(tblName As String)

On Error Resume Next

'******************************************************************************
'                                                                             *
'Date: 11/11/2010                                                             *
'Purpose: Deletes duplicate records in a single table.                        *
'                                                                             *
'Arguments:                                                                   *
'tblName > Your table.                                                        *
'                                                                             *
'******************************************************************************

Dim tempTbl As String
Dim tempSql As String
Dim db As DAO.Database
Set db = CurrentDb
tempTbl = db.TableDefs(tblName).Name

   If err.Number = 3265 Then
      MsgBox "Table does not exist!"
         GoTo Exit_Handle
   Else
      tempTbl = tblName & "2"
   End If

On Error GoTo Err_Handle

   tempSql = "SELECT DISTINCT *" & _
            " INTO " & tempTbl & _
            " FROM " & tblName

      db.Execute tempSql, dbFailOnError
      db.TableDefs.Delete (tblName)
      DoCmd.Rename tblName, acTable, tempTbl
      db.TableDefs.Refresh

Exit_Handle:
   db.Close
      Set db = Nothing
         Exit Function

Err_Handle:
   MsgBox err.Description
      Resume Exit_Handle

End Function '//LL

 

[vbaInet]

DELETE A.* FROM 
    (
    SELECT test_order.OrderID, test_order.Total 
    FROM test_order INNER JOIN 
        (
        SELECT Q.OrderID, Q.Total 
        FROM test_order AS Q 
        GROUP BY Q.OrderID, Q.Total
        HAVING Count(Q.*) > 1
        ) 
    AS P ON test_order.OrderID = P.OrderID AND test_order.Total = P.Total
    ) AS A;