asked on

advice on gas mileage query in a fleet management database

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.

Chrisedebo

All you need to do is compare the strvehicle in the prevmilage query to the strvehicle in the outer query.

and tlbgasrecords.strvehicle = mile1.strvehicle
 
'OR the complete query
 
SELECT Mile1.strVehicle, Mile1.dtmFillingDate AS Expr1, Mile1.sngNumOfGallons, Mile1.sngOdometer AS Expr2, (Select Max(sngOdometer) from tblGasRecords Where sngOdometer < Mile1.[sngOdometer] and tlbgasrecords.strvehicle = mile1.strvehicle) 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;

Open in new window

cracengl

ASKER

Thanks for the response. I think that got it closer to what I need. It just asked for the value of the vehicle I needed and only computed values for that vehicle. However, I have pasted the data I entered into the tblGasRecords to give a visual of what it is calculating.

Transaction #Vehicle      Filling Date      Source # Of Gallons Cost Per Gallon      Odometer
1      02-04      5/1/2006      Gas Card      14      $3.57      001,108
2      05-03      5/1/2007      Public Works      13      $3.45      004,108
3      03-00      5/1/2008      Public Works      13      $3.45      004,108
4      02-04      5/15/2008      Public Works      12      $3.57      004,409
5      02-04      5/2/2008      Public Works      13      $3.57      004,109
6      02-04      4/30/2008      Public Works      13      $3.57      003,750
7      02-04      5/25/2008      Public Works      10      $3.57      004,609
8      02-04      6/1/2008      Public Works      11      $3.57      004,809
9      02-04      6/13/2008      Public Works      12      $3.57      005,113
12      03-00      5/8/2008      Public Works      12      $3.45      004,408
13      02-04      6/16/2008      public Works      14      $3.45      005,345
14      03-00      5/10/2008      Public Works      12      $3.45      004,895

I changed the query code to what was suggested. When it asked for a value for strVehicle, I entered 03-00. This stands for Vehicle # 3. The -00 is just to signify the model year of the car; when we get new cars, they are reassigned numbers. By including the -model year, I can differentiate the different cars.

Here is what the query returned.

Vehicle Filling Date # Of Gallons Odometer PrevMileage Elapsed MPG
02-04 4/30/08 13 3750
03-00 5/1/2008 13 4108 3750 358 27.5384616851807
02-04 5/2/2008 13 4109
03-00 5/8/2008 12 4408 4109 299 24.9166660308838
03-00 5/10/2008 12 4895 4809 86 7.1666650772095
02-04 5/15/2008 12 4409
02-04 5/25/2008 10 4609

As you can see, the PrevMileage it used for car 03-00 on 5/1/2008 was 3750, but that PrevMIleage belongs to the record for car 02-04 from 4/30/2008. It makes this mistake for the other two readings as well, where it take the mileage from the record directly before it without distinguishing whether the value is for the same car or not.

Is there a way to fix this?

Chrisedebo

It shouldn't ask for the strvehicle when you execute the query.

Have you modified the query in any other way? if so, can you paste it here for me.

cracengl

ASKER

I opened my query in SQL view and pasted the query you wrote in the previous post.

I just went back and copied the new query. (after "and tlbgasrecords.strvehicle = mile1.strvehicle" was added to it)

Here is what it is as of right now:

SELECT Mile1.strVehicle, Mile1.dtmFillingDate AS Expr1, Mile1.sngNumOfGallons, Mile1.sngOdometer AS Expr2, (Select Max(sngOdometer) from tblGasRecords Where sngOdometer < Mile1.[sngOdometer] and tlbgasrecords.strvehicle = mile1.strvehicle) 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;

Chrisedebo

Can you do me a favour and upload an access db containing those two tables and your query so I can have a look. See whats going on.

Cheers

cracengl

ASKER

I exported those table and the query to a new database, but EE is not letting me attach it. I attempted to zip the file, but it still will not allow it.

Is there any other way of getting it to you?

Chrisedebo

Change the file's extension from mdb to txt then zip it up and try again.

or email it to me

chrisedebo (at) gmail (dot) com

cracengl

ASKER

My bad, I had to turn off the known file types option so I could rename it properly. Let me know if you can open it.

Thanks!
Fleet1.zip

Chrisedebo

I don't have access to 2007. Can you export the tables as csv or tab delimited text files? I will use the query syntax from this page.

cracengl

ASKER

Ok, here are the tables exported to text files. Let me know if this works, or I could always try to save the DB in a previous format if that would help.
tblGasRecords.txt
tblVehicleInfo.txt

Chrisedebo

Did you export these files as formatted text? It's difficult to get these files into access for processing.

Can you save the DB in a previous format? I have access 2003 so that or 2000 format would be fine.

cracengl

ASKER

I apologize for that.

Here it is in 2003 format, if you want to just change the extention from txt to mdb.

Thanks for your help.
maintenancescratch.txt

ASKER CERTIFIED SOLUTION

Chrisedebo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cracengl

ASKER

Awesome, that works! For some reason, it asks for the date parameters twice, but I think I could probably fix that. I also don't know what to do when, there is not a previous value to use for gas mileage, i.e. when there is not a record for a car before the one it is trying to find the mileage for...then it just doesn't perform a calculation. However, I think that with as much data as I will have to input, this won't be a big issue.

Thanks again for all of the help!

Chrisedebo

I would agree, not performing a calculation where the car doesn't have a previous record isn't a problem as long as you are aware that no value in the last 4 columns means it's the first entry.

You could also query this query to work out an average mpg over a period. eg monthly, quarterly etc.

Still, you have the basis of a good db there, keep at it and you'll end up with a useful tool :o)