Last Date does not display correctly

[Rod]

I have a vehicle database. I made an odometer entr table that has unit, date,
odometer and an autonumber field. When I set up a query to group by the unit
number and give me the last odometer and date, it returns the correct info
for most units but some of the units it "skips" back to a previous mnth for
the "last" date. I can't figure out why this is happening. Any help here?

 

[John Vinson]

I have a vehicle database. I made an odometer entr table that has unit, date,
odometer and an autonumber field. When I set up a query to group by the unit
number and give me the last odometer and date, it returns the correct info
for most units but some of the units it "skips" back to a previous mnth for
the "last" date. I can't figure out why this is happening. Any help here?

The LAST totals operator isn't much use. It returns the last record
*in disk storage order* - and Access can be very arbitrary about disk
storage order! It is NOT the same as the maximum date, or the most
recently entered record; I've never found a real use for Last in a
totals query.

Use a subquery to find the maximum date instead:

= (SELECT Max(X.[datefield]) FROM tablename AS X WHERE X.vehicleID =
tablename.vehicleID)


John W. Vinson[MVP]

 

[Rod]

I don't understand the subquery as I don't use them "ever" I'm trying your
suggestion many different ways but not doing something correct.

my table name is PM Odometer and the fields are "UNIT" and "PM Date" the
master vehicle table is called "vehicles"

Could you help me by wriiting out the sql for me?

I have a vehicle database. I made an odometer entr table that has unit, date,
odometer and an autonumber field. When I set up a query to group by the unit
number and give me the last odometer and date, it returns the correct info
for most units but some of the units it "skips" back to a previous mnth for
the "last" date. I can't figure out why this is happening. Any help here?

The LAST totals operator isn't much use. It returns the last record
*in disk storage order* - and Access can be very arbitrary about disk
storage order! It is NOT the same as the maximum date, or the most
recently entered record; I've never found a real use for Last in a
totals query.

Use a subquery to find the maximum date instead:

= (SELECT Max(X.[datefield]) FROM tablename AS X WHERE X.vehicleID =
tablename.vehicleID)


John W. Vinson[MVP]

 

[John Vinson]

I don't understand the subquery as I don't use them "ever" I'm trying your
suggestion many different ways but not doing something correct.

my table name is PM Odometer and the fields are "UNIT" and "PM Date" the
master vehicle table is called "vehicles"

Could you help me by wriiting out the sql for me?

To just *see* the latest record, try

SELECT [Unit], Max([PM Date]) As LatestPMDate
FROM [Vehicles]
GROUP BY [Unit];

This query will not be updateable, however, as it is a Totals query.

To get an updateable query showing only the latest date for each unit,
you can use the DMax() function rather than my suggested subquery:

SELECT [Unit], [PM Date]
FROM Vehicles
WHERE [PM Date] = DMax("[PM Date]", "[Vehicles]", "[Unit] = '" &
[Unit] & "'")

I'm assuming Unit is a Text field - if it's numeric, omit the ' and
the "'".

John W. Vinson[MVP]