One record from many based upon single field

[kemosabe]

I have a query that returns more than one record per unique field (AssocID).
I would like to return only 1 (first?) record per unique field. Is there an
easy way to do this? Thanks!

 

[Michel Walsh]

SELECT AssocID, LAST(otherField1), LAST(otherField2), LAST(... )
FROM somewhere
GROUP BY AssocID




Vanderghast, Access MVP

 

[John W. Vinson]

I have a query that returns more than one record per unique field (AssocID).
I would like to return only 1 (first?) record per unique field. Is there an
easy way to do this? Thanks!

SELECT TOP 1 AssocID, thisfield, thatfield, theotherfield
FROM <whatever>
ORDER BY <some field>

 

[Michel Walsh]

Ah, I haven't understood the question like this, and indeed, that returns
ONE record from the whole table.


Vanderghast, Access MVP

 

[cixelsyd]

I have a similar problem. I have a table containing transaction data for many
items (Partno). Each item may have several entries in the table due to
transactions on different dates (TDate).
I wish to select the complete record for each item for the most recent
transactions (TDate) from a table containing many records. How do I structure
the query?

Table

Partno1 Date1 DataX DataY
Partno1 Date2 DataX DataY
Partno1 Date3 DataX DataY > most recent date
Partno2 Date1 DataX DataY
Partno3 Date1 DataX DataY

I wish to return

Partno1 Date3 DataX DataY > most recent date
Partno2 Date1 DataX DataY
Partno3 Date1 DataX DataY

 

[Michel Walsh]

Four possible solutions at http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP