RLP said:
In a table, how do you delete dups using multiple columns as the criteria.
Example
State Year RC
Using those fields I want to remove any duplicates.
There are a couple of ways you could do this.
1. Make a new table based on the old table which only contains the unique
rows, and then scrub the old table's data and repopulate it with the new
table's data. To do this you need to use a make table query, and set the
query property to Unique Rows. This will only copy over 1 of the duplicated
records. Then remove the data from the original table (delete query) and
then append all the data from the new table back into the original table
(append query), then delete the new table.
All of the above steps are fairly easy to do via a macro.
This however relies on the assumption that if your above fields are
duplicated between records then every other field in those records is
duplicated as well. If this is true then thats fine and this method is
easy. Otherwise try the next method.
2. In code loop through each record and go looking for duplicates. Each
time you find a duplicated record delete it. Eventually you will be left
with no duplicates. I have appended some sample code below which does this.
There are *way* more efficient ways to implement this method, my code is
fairly blunt and simplistic but hopefully it is easy to understand which is
why I wrote it this way. It also has fairly crappy error checking.
You may need to make some adjustments to the sql string depending on your
field types. I assumed all 3 were strings, it will be easy to change the
strSQL if they are not. For example the code line below
"Year = """ & duplicateRst!Year & """ AND " & _
makes the following string if we assume year was 2004
Year = "2004"
If year is a number field then replacing the code with
"Year = " & duplicateRst!Year & " AND " & _
changes the string to the following
Year = 2004
You will also need to make sure you have DAO turned on. When you paste this
into your module check by going to the menu: Tools-->References. Look to
see if you have a tick by something with DAO in its description, if you dont
go and find it (it will be something like Microsoft DAO 3.6 Object library
but possibly with different numbers) and turn this reference on. Any issues
with references post back.
Note also that sometimes the news view adds unwanted new lines etc, you may
need to look for and delete these if you copy and paste this code from your
news reader.
Hope it helps
Regards
A
Private Sub RemoveDuplicates()
On Error GoTo RemoveDuplicates_Err
Dim db As DAO.database
Dim duplicateRst As DAO.Recordset
Dim deleteRst As DAO.Recordset
Dim strSQL As String
'Open the current database
Set db = CurrentDb
'Get a recordset listing the combos with duplicates
strSQL = "SELECT originalTable.State, originalTable.Year, originalTable.RC,
Count(originalTable.RC) AS CountOfRC " & _
"FROM originalTable " & _
"GROUP BY originalTable.State, originalTable.Year, originalTable.RC
" & _
"HAVING (((Count(originalTable.RC))>1));"
Set duplicateRst = db.OpenRecordset(strSQL, dbOpenSnapshot)
'Loop over each set of duplicates one by one, and for each combo with
duplicates delete all but one of the records
While (Not duplicateRst.EOF)
strSQL = "SELECT * FROM originalTable " & _
"WHERE State = """ & duplicateRst!State & """ AND " & _
"Year = """ & duplicateRst!Year & """ AND " & _
"RC = """ & duplicateRst!RC & """;"
Set deleteRst = db.OpenRecordset(strSQL)
If (Not deleteRst.BOF) Then
'Move to the last duplicate record in this set of duplicates, as
'this is required by Access to ensures RecordCount is valid
deleteRst.MoveLast
'Keep deleting the duplicate records until only 1 is left
While (deleteRst.RecordCount > 1)
'Delete the current record
deleteRst.Delete
deleteRst.MoveLast 'to ensure RecordCount is valid
Wend
End If
deleteRst.Close
duplicateRst.MoveNext 'move to the next set of duplicate records
Wend
RemoveDuplicates_Exit:
duplicateRst.Close
db.Close
Exit Sub
RemoveDuplicates_Err:
On Error Resume Next
MsgBox Err.Number & vbCrLf & Err.Description
End Sub
