asked on

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

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

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

Start Free Trial

Jeffrey Coachman

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...

Blueice13085

ASKER

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

Jeffrey Coachman

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

Blueice13085

ASKER

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;

Blueice13085

ASKER

Great Job!!

Blueice13085

ASKER

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:https://www.experts-exchange.com/questions/28266018/Ms-Access-Query-Help.html

#2: https://www.experts-exchange.com/questions/28266935/Ms-Access-Code-Error.html?anchorAnswerId=39572489#a39572489