asked on

Ms Access Coding and Query

I have 3 tables one tblVehicles, tblDrivers, and tblFuelLog

tblVehicles has following fields
VehicleId
DriverID
FuelId
VehicleNumber
StartOdo

tblDrivers has the following fields
DriverId
VehicleId
FuelId
FirstName
LastName
AssignedVehicle

tblFuelLog has the following fields
FuelId
DriverId
VehicleID
VehicleNumber
Driver
FillDate
FillTime
QtyGallons
OdoAtFillDate
PriceAtPump

What I need a Query or code to do is the following

I need it to take the StartOdo from tblVehicles as the base miles to start. Then each time a Driver puts in there OdoAtFillDate and QtyGallons for VehicleNmber I want it to get Miles Driver Drove then MPG per driver per truck

i.e. Data
tblVehicles
VehicleID = 1, StartOdo = 10
VehicleID = 2, StartOdo = 10

tblFuelLog
FuelId=1 VehicleId=1, DriverId=1, OdoAtFillDate = 30, QtyGallons = 5, PriceAtPump=$20.00
FuelId=2 VehicleId=1, DriverId=2, OdoAtFillDate = 20, QtyGallons = 5, PriceAtPump=$20.00
FuelId=3 VehicleId=1, DriverId=1, OdoAtFillDate = 40, QtyGallons = 5, PriceAtPump=$20.00
FuelId=4 VehicleId=1, DriverId=2, OdoAtFillDate = 50, QtyGallons = 5, PriceAtPump=$20.00
FuelId=5 VehicleId=2, DriverId=1, OdoAtFillDate = 30, QtyGallons = 5, PriceAtPump=$20.00
FuelId=6 VehicleId=2, DriverId=2, OdoAtFillDate = 20, QtyGallons = 5, PriceAtPump=$20.00
FuelId=7 VehicleId=2, DriverId=1, OdoAtFillDate = 40, QtyGallons = 5, PriceAtPump=$20.00
FuelId=8 VehicleId=2, DriverId=2, OdoAtFillDate = 50, QtyGallons = 5, PriceAtPump=$20.00

What I need Query to show
VehicleId=1, DriverId=1, totMiles = 20, totGallons = 10, totPrice=$40.00, MPG=2
VehicleId=2, DriverId=1, totMiles = 20, totGallons = 10, totPrice=$40.00, MPG=2
VehicleId=1, DriverId=2, totMiles = 20, totGallons = 10, totPrice=$40.00, MPG=2
VehicleId=2, DriverId=2, totMiles = 20, totGallons = 10, totPrice=$40.00, MPG=2

Hope this makes sense

chaau

It is not clear how you get the figure of totMiles = 20. Can you clarify

Blueice13085

ASKER

ok for VehicleId= 1 I did this

I took the StartOdo from the tblVehicles and subtract by the smallest OdoAtFillDate from tblFuelLog (OdoAtFillDate) 20-(StartOdo)10=(totMiles)10
Then I take each OdoAtFillUp from tblFuelLog and subtract by 2nd Biggest Value for VehicleId=1

so next would be (OdoAtFillDate) 30- (OdoAtFillDate)20 =(totMiles)10, then (OdoAtFillDate) 40- (OdoAtFillDate)30=(totMiles)10, then (OdoAtFillDate) 50- (OdoAtFillDate)40=(totMiles)10

so total miles for Vehicle ID (1) = 40
total miles for VehicleID (1), DriverID (1) = 20
total miles for VehicleID (1), DriverID (2) = 20

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

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

Start Free Trial

Hamed Nasr

Try:

Select   v.VehicleID
           , f.DriverID
           , (Max(f.OdoAtFillDate)-Min(f.OdoAtFillDate0 - Min(v.StartOdo)) aS totMiles
           , Sum(f.totGallons) As totGallons 
           , Sum(f.PriceAtPump) As totPrice
           , ( (Max(f.OdoAtFillDate)-Min(f.OdoAtFillDate0 - Min(v.StartOdo)) / Sum(v.totGallons)) As MPG
FROM tblVehicles As v INNER JOIN tblFuelLog As f ON v.VehicleID=f.VehicleID
GROUP BY v.VehicleID, f.DriverID

Open in new window

If you need the Driver name, then Join tblDrivers to the query.
You need to Group By any field added from tblDrivers.

For joins:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html

Blueice13085

ASKER

Seems to be working like I need it, I will try a few data and see how it goes.