Returning a quantity of records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a newbie question, but how do I get Access to return only 1 record
after a query. I'm basically using the query to filter to get records that
apply, then sort by date (descending). I only want to keep the most recent
record and export to Excel, but I don't know how to only query for one
record. Any info would help

Thanks
 
Use the Top clause in your query.

Sort so the record you want to KEEP is at the top of the list
Then in the query's properties set the top property to 1 record.

In SQL text that would look like
SELECT TOP 1 FieldA, FieldB, FieldC
FROM YourTable
WHERE Field1 > 20
ORDER BY FieldDate Desc, PrimaryKeyField

The order by clause has to ensure that there are no ties, so if my sort won't
produce unique values then I usually throw in the primary key. If there are
ties, Access returns the ties.

For example
AA
AA
AB

Top 1 sorted Ascending on that would return two rows since AA and AA are equal
and therefore both are returned.
 
Back
Top