How to extract unique records in Access 1

[henio]

Hi all,

I have a recordset that looks like:

Col 1 Col 2

A 1
A 2
A 3
B 4
B 5
C 2
C 3
etc.

I want to extract unique entries from Col 1 and the first associated vale from Col 2.

i.e.

A 1
B 4
C 2

I can do this easily in Excel but I have far too many records in the Access db to export to Excel.

Thanks,
Henio

 

[MoiraT]

You can create a query in Access. Drag your fields onto the query grid then click on the Totals button on the toolbar (like a Greek character).
For the first field (A, B, C) select Group By in the Total line of the query grid. For the second field select First in the Total line of the query grid.
Hope that helps

 

[JohnAcc]

Have you used the Get External Data option in excel - you can import your data from an access db directly into a pivot table and you can specify which criteria to use.

Rgds, John

 

[nberryman]

FYI: Are you aware that there are several Access Forums in Tek-Tips.

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[henio]

MoiraT,

thanks for your quick response. Because Col 2 is not sorted (as I had taken the precaution to do in Excel), 'first' didn't work, but 'min' did. Thanks for pointing me in the right direction.

Henio

 

[sdraper]

FOr a one time solution you can copy the table that the data you want unique values comes from. CHoose structure only. Change the field that you want unique values from in the new table to "indexed - no duplicates". do an append query from the original table to the new empty table. You will get an error message telling you that all of the data can't be copied but that's OK. The new table will be populated with unique values. I have only done this with Access 97 but I would imagine it would work with all version. Hope this helps.:

Sam