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

chaauCommented:
It is not clear how  you get the figure of totMiles = 20. Can you clarify
0
Blueice13085Author Commented:
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
0
chaauCommented:
What you require first is to calculate "driven miles since last fill-up". I have created a small test database (attached)
The first query dryDrivenMiles has this syntax:
SELECT 
tblFuelLog.FuelID, 
tblFuelLog.VehicleId, 
tblFuelLog.OdoAtFillDate, 
[tblFuelLog].[OdoAtFillDate]-Nz((SELECT MAX(OdoAtFillDate) 
    FROM tblFuelLog a 
    WHERE a.OdoAtFillDate < tblFuelLog.OdoAtFillDate AND 
        a.VehicleID = tblFuelLog.VehicleID),[tblVehicles].[StartODO]) AS drivenMiles
FROM tblFuelLog 
    INNER JOIN tblVehicles ON tblFuelLog.VehicleId = tblVehicles.VehicleID;

Open in new window

This is your "drivenMiles"

And the second query is your result:
SELECT 
tblFuelLog.VehicleId, 
tblFuelLog.DriverID, 
Sum(dryDrivenMiles.drivenMiles) AS SumOfdrivenMiles, 
Sum(tblFuelLog.QtyGallons) AS SumOfQtyGallons, 
Sum(tblFuelLog.PriceAtPump) AS SumOfPriceAtPump, Sum([drivenMiles])/Sum([QtyGallons]) AS MPG
FROM dryDrivenMiles INNER JOIN 
   tblFuelLog ON dryDrivenMiles.FuelID = tblFuelLog.FuelID
GROUP BY tblFuelLog.VehicleId, tblFuelLog.DriverID;

Open in new window

TEST.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
hnasrCommented:
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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html
0
Blueice13085Author Commented:
Seems to be working like I need it, I will try a few data and see how it goes.
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.