Access2000: Query

[Arvi Laanemets]

Hi

I have a table where routes for cars are registered
Routes: RouteID, EndTime, CarID, ..., EndKm
(EndKm is total mileage for particular car after route, and is entered into
table by user)

I have a query (p.e. Q1, which returns total mileage for every car in Routes
table after latest
route for every month:

SELECT b.MonthStr, a.CarID, a.EndKm As KmX
FROM Routes AS a, Q2 AS b
WHERE a.EndTime= b.TimeX AND a.CarID=b.CarID;

, where Q2:
SELECT Format$([Routes].[EndTime],'yyyy\.mm') AS MonthStr, Routes.CarID,
MAX(Routes.EndTime) AS TimeX
FROM Routes
GROUP BY Format$([Routes].[EndTime],'yyyy\.mm'), Routes.CarID;

This all works. But I need now another column in query Q1, where starting
mileage for those cars and months is calculated. The rules for such
calculation are:
- the starting mileage (Km0) will be equal to latest existing mileage
entry in Routes table for this car from all previous months.
- when there were no entries for this car before the month, calculation
is made for, the Km0 equals to according mileage value from special starting
parameters table (StartValues)

StartValues: EntryID, CarID, Date, Km, ...
(any car can have several entries here - it depends on date, which one is
taken in account)

I.e. the query Q1 must be something like
SELECT b.MonthStr, a.CarID, <Expression> As Km0, a.EndKm As KmX
FROM Routes AS a, Q2 AS b, ...
WHERE a.EndTime= b.TimeX AND a.CarID=b.CarID ...;


Has anyone some idea for best way to achieve this?
Thanks in advance!
Arvi Laanemets