Trying to select the last entered mileage for a vin number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jamie P - 30 Aug 2005 23:14 GMT

I am having trouble generating a query that will only give the most recent
vehical mileage (vin number) that was recorded on the [tblMileage]. I need
to use that mileage in several other areas. I need alot of help for this one
I have been fighting with this for awhile. Please help..... I enclosed a
copy of the SQL of the query.

SELECT DISTINCTROW First([qryMileage Query].[Date By Month]) AS [FirstOfDate
By Month], [qryMileage Query].[Vin Number], [qryMileage Query].[Sum Of
Mileage]
FROM [qryMileage Query]
GROUP BY [qryMileage Query].[Vin Number], [qryMileage Query].[Sum Of Mileage],
Year([qryMileage Query].[Current Month])*12+DatePart('m',[qryMileage Query].
[Current Month])-1;

Reply to this Message

Ken Sheridan - 31 Aug 2005 00:28 GMT

The way to do this sort of thing is to use a subquery which is correlated
with the outer query. The subquery finds the most recent date for the
current vehicle. The following would give you the rows from tblMileage for
the most recent date for each vehicle in a column MileageDate in the table:

SELECT T1.*
FROM tblMileage AS T1
WHERE T1.MileageDate =
(SELECT MAX(T2.MileageDate)
FROM tblMileage AS T2
WHERE T2.[Vin Number] = T1.[Vin Number]);

As you see the two instances of the table are distinguished by the aliases
T1 and T2 so that the subquery can find the latest (MAX) date from the table
where the Vin Number is the same as the current Vin Number in the instance of
the table in the outer query.

> I am having trouble generating a query that will only give the most recent
> vehical mileage (vin number) that was recorded on the [tblMileage]. I need
[quoted text clipped - 9 lines]
> Year([qryMileage Query].[Current Month])*12+DatePart('m',[qryMileage Query].
> [Current Month])-1;

Reply to this Message