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.
cracenglAsked:
Who is Participating?
 
ChrisedeboConnect With a Mentor Commented:
many apologies, it appears a small typo prevented you from running the query. But I couldn't spot it.....

tlbgasrecords.strvehicle = mile1.strvehicle

should be

tblgasrecords.strvehicle = mile1.strvehicle

it's easy when you know how....

ok, try the below query.
SELECT Mile1.strVehicle, Mile1.dtmFillingDate AS Expr1, Mile1.sngNumOfGallons, Mile1.sngOdometer AS Expr2, (Select Max(sngOdometer) from tblGasRecords Where sngOdometer < Mile1.[sngOdometer] and tblgasrecords.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

0
 
ChrisedeboCommented:
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

0
 
cracenglAuthor Commented:
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?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ChrisedeboCommented:
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.
0
 
cracenglAuthor Commented:
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;
0
 
ChrisedeboCommented:
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
0
 
cracenglAuthor Commented:
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?
0
 
ChrisedeboCommented:
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
0
 
cracenglAuthor Commented:
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
0
 
ChrisedeboCommented:
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.
0
 
cracenglAuthor Commented:
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
0
 
ChrisedeboCommented:
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.
0
 
cracenglAuthor Commented:
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
0
 
cracenglAuthor Commented:
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!
0
 
ChrisedeboCommented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.