1

I'm using Access 2007.

I have a table with about 20 fields. One of those fields is an autonumber ID, so it's unique. I have a lot of records in this table that only differ in their autonumber ID. I can't just delete the ones with odd or even IDs, because some pairs of duplicates have both odds or both evens. Any ideas on how to select one record from each pair for deletion?

I know this could probably be done with VBA, but I'm not really familiar with Access VBA yet, so I'm looking for a purely SQL-based solution.

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
sigil
  • 9,370
  • 40
  • 119
  • 199

2 Answers2

3
DELETE *
FROM yourTable
WHERE id NOT IN
  ( SELECT min(id)
    FROM yourTable
    GROUP BY field2
           , filed3
           , field4
           , etc...      <--- all other fields, except id
  )
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
2

delete the ones that are not min(id), grouped by the other columns that mean equivalence.

Randy
  • 16,480
  • 1
  • 37
  • 55
  • I couldn't figure out how to use min() to get what I wanted, so I ended up using this: http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows/18934#18934 But min() looks useful, thanks for pointing me at it. – sigil Apr 11 '11 at 22:28