Solved

advice on gas mileage query in a fleet management database

Posted on 2008-06-18
15
630 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:cracengl
  • 8
  • 7
15 Comments
 
LVL 7

Expert Comment

by:Chrisedebo
Comment Utility
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
 

Author Comment

by:cracengl
Comment Utility
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
 
LVL 7

Expert Comment

by:Chrisedebo
Comment Utility
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
 

Author Comment

by:cracengl
Comment Utility
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
 
LVL 7

Expert Comment

by:Chrisedebo
Comment Utility
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
 

Author Comment

by:cracengl
Comment Utility
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
 
LVL 7

Expert Comment

by:Chrisedebo
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:cracengl
Comment Utility
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
 
LVL 7

Expert Comment

by:Chrisedebo
Comment Utility
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
 

Author Comment

by:cracengl
Comment Utility
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
 
LVL 7

Expert Comment

by:Chrisedebo
Comment Utility
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
 

Author Comment

by:cracengl
Comment Utility
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
 
LVL 7

Accepted Solution

by:
Chrisedebo earned 250 total points
Comment Utility
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
 

Author Comment

by:cracengl
Comment Utility
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
 
LVL 7

Expert Comment

by:Chrisedebo
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Read about achieving the basic levels of HRIS security in the workplace.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now