Query between two tables problem

[kiliev]

Hi there!

I came upon the next query problem: I have a two tables -

one for refueling of our vehicles that I am extracting from Shell's site

TblFuel
PlateNumber Date Mileage Liters Price
C 4199 MK 15/03/2011 120511 37.52 75.52
C 4203 HA 16/03/2011 156549 31.74 64.41
CA 0667 TM 16/03/2011 47321 48.03 91.05
C 4199 MK 18/03/2011 121010 35.43 71.36
CA 0667 TM 20/03/2011 48053 46.16 91.05
C 4203 HA 22/03/2011 157073 30.75 64.41
etc...

other table for Driver to vehicle allocation

tblVehicles_details
PlateNumber DateFrom DateTo DriverName
C 4199 MK 01/01/2009 17/03/2011 John Smith
C 4203 HA 01/01/2009 19/03/2011 Steven Cole
CA 0667 TM 20/03/2011 Steven Cole
C 4199 MK 18/03/2011 Robert Jackson
C 4203 HA 20/03/2011 John Smith
etc...

What I need is a query that will show the mileage of each driver like total by periods, no matter of what vehicle he/she was driving during the periods. The Query will be visualised as PivotChart View.
There are two other tables - tblVehicles - where each vehicle is unique record, and tblDrivers - where each driver is unique record as well.

Regards,
Kalin

 

[lameid]

I think you want the miles each driver drove based on mileage recorded?

Assuming there is a fuel up at the begining of the trip....

Qry Driver Miles By Vehichle

Select tblVehicles_details.DriverName,
   tblVehicles_details.Platenumber,
   Max(TblFuel.Mileage) - Min(TblFuel.Mileage) as Miles

From tblVehicles_details
 Inner Join TblFuel 
   On TblFuel.Platenumber = tblVehicles_details.Platenumber
Where TblFuel.[Date] Between tblVehicles_details.DateFrom and tblVehicles_details.Dateto
Group By tblVehicles_details.DriverName,
   tblVehicles_details.Platenumber

Select DriverName, Sum(Miles) as Total_Miles
From [Qry Driver Miles By Vehichle]
Group By DriverName

You mention by period so what I am missing is what period? Month, day.... And since my premise is flawed... how are you going to split the miles if the recording spans drivers or periods?

Outside of the problem presented, I would think the correct answer is to pull the drivers logs.... unless you are trying to verify somehow.

 

[kiliev]

Thank you, lameid!

Your code has solved the problem and now this PivotChart graph works just fine.