advice on gas mileage query in a fleet management database
Posted on 2008-06-18
I need advice on a query I am running in Access 2007 that calculates gas mileage. The following is the query information:
SELECT Mile1.strVehicle, Mile1.dtmFillingDate AS Expr1, Mile1.sngNumOfGallons, Mile1.sngOdometer AS Expr2, (Select Max(sngOdometer) from tblGasRecords Where sngOdometer < Mile1.[sngOdometer]) AS PrevMileage, [sngOdometer]-[PrevMileage] AS Elapsed, ([sngOdometer]-[PrevMileage])/[sngNumOfGallons] AS MPG
FROM tblGasRecords AS Mile1
WHERE (((Mile1.dtmFillingDate) Between [Start Date] And [End Date]))
ORDER BY Mile1.strVehicle;
My problem is that in the table tblGasRecords, there are gas records assigned to different vehicles. We have a total of about 90 cars. With the query set up the way, it is taking the odometer value of the highest Odometer reading < Max Odometer; the problem I find, is that if the query runs for say Car #3, and an odometer value for Car #2 has been entered in the record before it, the query is taking the value from Car #2 to computer the miles per gallon for Car #3.
I am not sure if I need to set up a Gas Record table for each individual car to keep the data from the different car numbers segregated or not. This seems like a lot of work; I think there must be a better way, perhaps a different way of writing the query. I also thought that if I could somehow set up a new field in the table itself to calculate gas mileage, that would make the query process a lot easier.
Also, I only want to ultimately run this query on a monthly basis for each car. I am not sure if I need to set up a query that just runs for 1 car only and then use a union query to combine them all into one or not. Is there a way to automate it for a certain date range where it will cycle through all of the cars? A macro perhaps?
I am new to Access, queries, functions, etc. so please excuse my lack of knowledge. I appreciate greatly any help you can provide.