MS Access Query Help

I have a query called qryFuelDataByVehicle with the following data

 SELECT tblFuelLog.VehicleId, tblFuelLog.VehicleNumber, tblVehicles.StartOdo AS StartOdo1, Max(tblFuelLog.OdoAtFillDate) AS MaxOfOdoAtFillDate, Min(tblFuelLog.OdoAtFillDate) AS MinOfOdoAtFillDate, [MaxOfOdoAtFillDate]-[StartOdo] AS VehTotalOdo, Sum(tblFuelLog.QtyGallons) AS TotalGallons, IIf([TotalGallons]=0,0,[VehTotalOdo]/[TotalGallons]) AS MPG
 FROM tblVehicles INNER JOIN tblFuelLog ON tblVehicles.VehicleNumber = tblFuelLog.VehicleNumber
 GROUP BY tblFuelLog.VehicleId, tblFuelLog.VehicleNumber, tblVehicles.StartOdo;

I have a query called qryFuelDataByVehicleByDriver with the following data

 SELECT qryFuelDataByVehicle.VehicleId, qryFuelDataByVehicle.VehicleNumber, tblFuelLog.DriverId, tblFuelLog.Driver, tblFuelLog.OdoAtFillDate, qryFuelDataByVehicle.StartOdo1, qryFuelDataByVehicle.MaxOfOdoAtFillDate, qryFuelDataByVehicle.MinOfOdoAtFillDate, IIf([OdoAtFillDate]=[MaxOfOdoAtFillDate],[MaxOfOdoAtFillDate]-[MinOfOdoAtFillDate],IIf([OdoAtFillDate]=[MinOfOdoAtFillDate],[MinOfOdoAtFillDate]-[StartOdo1],[OdoAtFillDate]-[StartOdo1])) AS DriverMiles, tblFuelLog.QtyGallons
 FROM qryFuelDataByVehicle INNER JOIN (tblVehicles INNER JOIN tblFuelLog ON tblVehicles.VehicleNumber=tblFuelLog.VehicleNumber ) ON qryFuelDataByVehicle.VehicleId=tblVehicles.Vehicle ID
 GROUP BY qryFuelDataByVehicle.VehicleId, qryFuelDataByVehicle.VehicleNumber, tblFuelLog.DriverId, tblFuelLog.Driver, tblFuelLog.OdoAtFillDate, qryFuelDataByVehicle.StartOdo1, qryFuelDataByVehicle.MaxOfOdoAtFillDate, qryFuelDataByVehicle.MinOfOdoAtFillDate, tblFuelLog.QtyGallons;



 I am trying to get qryFuelDataByVehicleByDriver to calculate the mpg for each driver and each truck, the tblVehicles.StartOdo will be the odometer the truck starts with from the get go (this miles is with the truck filled up)

do the problem I think is i am grouping by truck, by driver, so what i need is i.e.
Driver       VehicleNumber         OdoAtFillUp     QtyGallons
Mike              1511-V                            150000           2
Mike              1511-V                            150010           2
Mike              1511-V                            150020           2
John               1511-V                            150030          2
Mike              1511-Q                            150000          2
Mike              1511-Q                            150010          2
Mike              1511-Q                            150020          2
John              1 511-Q                            150030          2

 John Drove 10 Miles in 1511-Q for 2 gallons of fuel this would be 5 MPG
Mike Drove 10 Miles in 1511-Q for 2 gallons of fuel this would be 5 MPG
John Drove 10 Miles in 1511-V for 2 gallons of fuel this would be 5 MPG
Mike Drove 10 Miles in 1511-V for 2 gallons of fuel this would be 5 MPG
Blueice13085Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Sample DB can help us.
In your sample you have:
Mike              1511-V                            150020           2
John               1511-V                            150030          2
Are you expecting
Mike               1511-V                            5
or there should be John?
Look at sample (Query1). May be it could be some base for your query
DB-MPG.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
So there is no data/time data tracked in this system?

Typically something like this would be filtered by a time period.
This way you would only have 1 starting record and 1 ending record for each Driver/Vehicle/Period
Then it is quite easy to get the min and max readings for that period, then calculate the difference, ...then calc the MPG.
...then concatenate the fields for the formatted string you want...
0
Blueice13085Author Commented:
No, really it is used to track stealing of fuel, so the StartOdo will be the start miles of the vehicle with a full tank. the problem is Drivers can alter mileage, but if you track using only the miles at fill up its harder to alter

I also have a DateAtFill up field if that would be useful?

Als315 can you please convert to .mdb I am running A2003
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS LiasonCommented:
Blueice13085,
<I also have a DateAtFill up field if that would be useful?>
Probably, ...but I just thought I would mention that...

als315 is a great expert, so I will step aside now and let you continue on with him (to avoid confusion)

;-)

JeffCoachman
0
Blueice13085Author Commented:
Als315, great job....made me think of the way i was trying to do it. your way got me to delete one field off my program....

qryDataByVehicle:

SELECT tblFuelLog.VehicleId, tblFuelLog.VehicleNumber, Max(tblFuelLog.OdoAtFillDate) AS MaxOdo, Min(tblFuelLog.OdoAtFillDate) AS MinOdo, [MaxOdo]-[MinOdo] AS VehTotalOdo, Sum(tblFuelLog.QtyGallons) AS TotalGallons, IIf([TotalGallons]=0,0,[VehTotalOdo]/[TotalGallons]) AS MPG
FROM tblFuelLog
GROUP BY tblFuelLog.VehicleId, tblFuelLog.VehicleNumber
ORDER BY tblFuelLog.VehicleNumber;


qryDataByVehicleByDriver:

SELECT tblFuelLog.FillDate, tblFuelLog.VehicleNumber, tblFuelLog.Driver, tblFuelLog.OdoAtFillDate, (Nz(DMin("OdoAtFillDate","tblFuelLog","OdoAtFillDate>" & [OdoAtFillDate] & " And VehicleNumber = " & Chr(34) & [VehicleNumber] & Chr(34)),0)-[OdoAtFillDate]) AS DriverMiles, tblFuelLog.QtyGallons, (Nz(DMin("OdoAtFillDate","tblFuelLog","OdoAtFillDate>" & [OdoAtFillDate] & " And VehicleNumber = " & Chr(34) & [VehicleNumber] & Chr(34)),0)-[OdoAtFillDate])/[QtyGallons] AS MPG
FROM tblFuelLog
GROUP BY tblFuelLog.FillDate, tblFuelLog.VehicleNumber, tblFuelLog.Driver, tblFuelLog.OdoAtFillDate, tblFuelLog.QtyGallons, (Nz(DMin("OdoAtFillDate","tblFuelLog","OdoAtFillDate>" & [OdoAtFillDate] & " And VehicleNumber = " & Chr(34) & [VehicleNumber] & Chr(34)),0)-[OdoAtFillDate])/[QtyGallons]
HAVING ((((Nz(DMin("OdoAtFillDate","tblFuelLog","OdoAtFillDate>" & [OdoAtFillDate] & " And VehicleNumber = " & Chr(34) & [VehicleNumber] & Chr(34)),0)-[OdoAtFillDate])/[QtyGallons])>0))
ORDER BY tblFuelLog.VehicleNumber, tblFuelLog.Driver;
0
Blueice13085Author Commented:
Great Job!!
0
Blueice13085Author Commented:
The problem still needs more, I have updated the Question at the below links, anyone that gets the answer on either page gets credit for both!

#1:http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28266018.html

#2: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28266935.html#a39572489
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.