Mileage Calculation

Friday

Registered User.
Local time
Today, 18:27
Joined
Apr 11, 2003
Messages
542
Folks, I'm not even sure how to ask this correctly. I have an inherited db that I have screamed each year for three years. Vehicle mileage db. Original author used wizards and macros to design and operate this db, and I have finally gotten around to re-designing it. Scrapped the old design completely. I know have two tables:

tblVehicles:
ID​
fldUnit​

tblMileage:
fldUnit​
fldYear (YYYY)​
fldMonth (MM)​
fldOdometer​

tblVehicles.fldUnit linked to tblMileage.fldUnit

Figuring mileage each month is simple, except for December, which is the odometer reading for December subtracted from the odometer reading for January of the following year. This is the part I cannot get my brain around. Obviously that figure is not gathered until January is over. What I cannot seem to pin down is how to gather all this data into one report, mainly because of the December calculation. Any ideas on this?
 
Plug this into a new query and see if it offers any ideas. I think you want to subtract December from December

SELECT tblMileage.fldUnit, tblMileage.fldMonth, tblMileage.fldYear, tblMileage_1.fldMonth, tblMileage_1.fldYear, tblMileage.fldODO-tblMileage_1.fldODO AS TotalODO
FROM tblMileage INNER JOIN tblMileage AS tblMileage_1 ON tblMileage.fldUnit = tblMileage_1.fldUnit
WHERE (((tblMileage.fldMonth)=12) AND ((tblMileage.fldYear)=2006) AND ((tblMileage_1.fldMonth)=12) AND ((tblMileage_1.fldYear)=2006-1));

You can replace the year (2006) with a parameter, and reuse.
 
Thanks, Lagbolt, I'll give it a whirl. But what I want is to subtract December of 2006 from January of 2007.
 
lagbolt: I got it. Your query got me on the right track. Thanks so much. :)
 
Cheers,
Glad you found something you can use.
 

Users who are viewing this thread

Back
Top Bottom