Return entire row which contains max value
Posted on 2007-12-06
In an MS Access Query, How do I return the entire row which contains the max() value of some field.
For instance, let's say I have a CustomerID field and an invoice_date field and the rest of the record, whatever it may be. There may be many records with this customerID, but I want to return the entire record that contains the newest (or oldest) invoice_date.
I usually do this with two queries, one to find the max(or min) date for the customer, then a second query to pull the entire record based on a two filed join on the customerID and invoice_date fields.
Is there a one-step way of doing this or is this the way that everyone does this?