Determining the most recent record

[T. Wilson]

Hello,

Assuming a table with multiple fields, one of which contains the date, is
there a way to structure an Access query so that it returns only the one
most recent record?

For example, a table with the following fields...
Date
VehicleID
ServiceDescription

with the following sample records...
01/01/2004 01001 Oil Change
02/03/2004 01001 Oil Change
02/10/2004 01001 Air Filter Change

If I wanted to know when the most recent Oil Change occured, could I
structure a query that would return just that one record?

Thanks,

Todd

 

[Kirby Alexander]

-----Original Message-----
Hello,

Assuming a table with multiple fields, one of which contains the date, is
there a way to structure an Access query so that it returns only the one
most recent record?

For example, a table with the following fields...
Date
VehicleID
ServiceDescription

with the following sample records...
01/01/2004 01001 Oil Change
02/03/2004 01001 Oil Change
02/10/2004 01001 Air Filter Change

If I wanted to know when the most recent Oil Change occured, could I
structure a query that would return just that one record?

Thanks,

Todd



.
Create a query off of that query with only the fields Oil

Change and Date in it. Then Sort it by Date and use
grouping to group it by last in the date field. This will
give you the last date entered.

 

[Allen Browne]

See:
Queries: Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
for 4 ways to do this.

Depending on your needs, the simplest approach might be a query into the
Vehicle table, with a sub-query that gets the most recent service date (but
the result would be read-only).

 

[John Vinson]

Hello,

Assuming a table with multiple fields, one of which contains the date, is
there a way to structure an Access query so that it returns only the one
most recent record?

Two ways:

- If you don't need to update the resulting recordset use a Subquery:
on the Criteria line under the datefield put

=(SELECT Max([datefield]) FROM Tablename)

- If you want to update the resultant recordset use the (somewhat less
efficient) DMax() function instead:

=DMax("[datefield]", "[tablename]")

Either of these can take criteria if you (say) want to find the most
recent maintenance for a particular vehicle.

 

[T. Wilson]

Gentlemen,

Thank you for your help!

Sincerely,
Todd Wilson

Hello,

Assuming a table with multiple fields, one of which contains the date, is
there a way to structure an Access query so that it returns only the one
most recent record?

Two ways:

- If you don't need to update the resulting recordset use a Subquery:
on the Criteria line under the datefield put

=(SELECT Max([datefield]) FROM Tablename)

- If you want to update the resultant recordset use the (somewhat less
efficient) DMax() function instead:

=DMax("[datefield]", "[tablename]")

Either of these can take criteria if you (say) want to find the most
recent maintenance for a particular vehicle.

 

[John Spencer (MVP)]

John V,
Question?

Why do you say that using a subquery in criteria makes the query not updatable?
The following query is updatable for me.

SELECT FAQ.fID, FAQ.fPriority, FAQ.fSubject, FAQ.fText, FAQ.fKeywords, FAQ.temp
FROM FAQ
WHERE (((FAQ.fID)=(Select Max(fID) From FAQ)));

I realize that if I use an SQL aggregate function in the SELECT clause that this
query would not be updatable, but if I am using it only in the WHERE clause it
seems to be fine.

With all respect to the master

John S

Hello,

Assuming a table with multiple fields, one of which contains the date, is
there a way to structure an Access query so that it returns only the one
most recent record?

Two ways:

- If you don't need to update the resulting recordset use a Subquery:
on the Criteria line under the datefield put

=(SELECT Max([datefield]) FROM Tablename)

- If you want to update the resultant recordset use the (somewhat less
efficient) DMax() function instead:

=DMax("[datefield]", "[tablename]")

Either of these can take criteria if you (say) want to find the most
recent maintenance for a particular vehicle.

 

[John Vinson]

John V,
Question?

Why do you say that using a subquery in criteria makes the query not updatable?
The following query is updatable for me.

SELECT FAQ.fID, FAQ.fPriority, FAQ.fSubject, FAQ.fText, FAQ.fKeywords, FAQ.temp
FROM FAQ
WHERE (((FAQ.fID)=(Select Max(fID) From FAQ)));

I realize that if I use an SQL aggregate function in the SELECT clause that this
query would not be updatable, but if I am using it only in the WHERE clause it
seems to be fine.

Must have been fixed in a more recent version! In my experience, any
query containing any aggregate function is not updateable. I'll try it
out, thanks for the heads-up!