-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Ah... Maybe one of these (these qrys assume there is only one odometer
reading per period):
SELECT VehicleID, [Period], Odometer, [Odometer] - DLookup("[Odometer]",
"YourTable", "[Period] = " & [Period]-1 & " AND VehicleID=" &
[VehicleID]) AS Annual
FROM YourTable
ORDER BY VehicleID, [Period] DESC
Same version using a subquerey (might be faster 'cuz can use indexes):
SELECT VehicleID, [Period], Odometer,
Odometer - (SELECT Odometer FROM YourTable WHERE Period = T.Period-1 AND
VehicleID = T.VehicleID) AS Annual
FROM YourTable As T
ORDER BY VehicleID, [Period] DESC
If the periods are not sequential, try this:
SELECT VehicleID, [Period], Odometer,
Odometer - (SELECT Odometer FROM YourTable WHERE
Period = (SELECT MAX(Period) FROM YourTable WHERE Period < T.Period)
AND VehicleID = T.VehicleID) AS Annual
FROM YourTable As T
ORDER BY VehicleID, [Period] DESC
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQQ/W0oechKqOuFEgEQLLwwCgmN1Q9nNb0q3ampd7G+NsRlHMEqMAo P0W
yrK4hG0fiRpFvrfhcsSx2hYl
=Y5fn
-----END PGP SIGNATURE-----
John Young wrote:
[color=blue]
> MGFoster ...
>
> your amendment works for giving running totals as you describe. but
> what i am look ing for is annual total ie difference between this
> years and last years figure
> i.e. 2003 year was 40000 LAST year was 35000 giving a "annual total" of
> 5000....
> ...as you assumed all data is numeric..
>
> I have tried various formula / criteria and have so far got no
> satisfactory result ....
>
> NOTE... need for this figure is for annual transportation department
> returns for each vehicle[/color]
