Removing duplicates in MS Access 2010 (like in Excel)

Question

I currently have some MS Access tables which are too large to be handled using Excel. I basically need to know how to remove duplicates based on one column, thus deleting lines and keeping everything else unchanged. Example:

This is an example of how my table is

a1 (...) (...) 1 (...) (...)

a1 (...) (...) 2 (...) (...)

a1 (...) (...) 3 (...) (...)

a1 (...) (...) 4 (...) (...)

a1 (...) (...) 6 (...) (...)

a1 (...) (...) 7 (...) (...)

b1 (...) (...) 8 (...) (...)

b1 (...) (...) 9 (...) (...)

b1 (...) (...) 10 (...) (...)

c1 (...) (...) 11 (...) (...)

c1 (...) (...) 12 (...) (...)

This is what I need:

a1 (...) (...) 1 (...) (...)

b1 (...) (...) 8 (...) (...)

c1 (...) (...) 11 (...) (...)

In excel I would just use the function remove duplicates and select the first column.

How can I do this in MS Access 2010?

Thank you!

Answer 1

You could just write an aggregate query and turn it into a make-table action query:

SELECT colLetter, Min(colNumber) As newColNumber
INTO newtable
FROM tableName
GROUP BY colLetter

Or use a DISTINCT clause with DLookUp or DMin:

SELECT DISTINCT colLetter,
DLookUp("colNumber", "tableName", "colLetter='" & tableName.colLetter & "'") As newColNumber
INTO newtable
FROM tableName

Altogether, unlike Excel, you have to understand the structure of your data in Access as records process together and do not operate independently like spreadsheet cells.