Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Question Unanswered: Calculations between records

    I have a database for tracking vehicle costs. I want to be able to calculate fuel consumption for each tank of fuel and display in a report.

    The only information I could find was for Access 2000 where you add the table twice and do calculations between field and field_1. I tried this in Access 2003 and could not get it to work.

    I have two fields I want to use for the calculations: Date (being the day I bought fuel) and Odometer (reading on the day fuel purchased). For date I want to calculate the number of days between fill ups (current record - previous record). For Odometer I want to calculate the distance travelled between fill ups. Then I can do some further calcs.

    Any suggestions?

    Paul

  2. #2
    Join Date
    Apr 2004
    Posts
    173
    I think I've done just what you are talking about. I did it with sub select statements in SQL.

    Basically it works like this.

    SELECT x y z (SELECT a FROM mileages WHERE some key field ='s the key field in the outer table)

    You can use this technique to return values from the same table that correlate to the outer record. Then just do the math with the value you picked up in the sub select statement to return what you are looking for.

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This give you any ideas?

    SELECT FuelPurchases.Mileage, FuelPurchases.gallons, FuelPurchases.FillDate, (SELECT Max(Mileage) FROM FuelPurchases AS Alias WHERE Alias.Mileage < FuelPurchases.Mileage) AS PrevMileage
    FROM FuelPurchases;
    Paul

  4. #4
    Join Date
    Jul 2005
    Posts
    39
    A possible way is if your base table here called tblTripLog has (recordNo, vehicleNo, fillDate, odometer, fuelVolume) then a predecessor query called qryShiftTripLog as:

    Code:
    SELECT [recordNo]-1 AS shiftNo, tblTripLog.vehicleNo, tblTripLog.odometer,
    tblTripLog.fillDate, tblTripLog.fuelVolume
    FROM tblTripLog;
    to use in the final query called qryTripLogAnalysis as:

    Code:
    SELECT tblTripLog.recordNo, tblTripLog.vehicleNo, 
    qryShiftTripLog.odometer-tblTripLog.odometer AS journey, 
    qryShiftTripLog.fuelVolume-tblTripLog.fuelVolume AS fuelUsed,
    [journey]/[fuelUsed] AS mpg, [fuelUsed]/[journey] AS gpm
    FROM tblTripLog INNER JOIN qryShiftTripLog 
    ON tblTripLog.recordNo=qryShiftTripLog.shiftNo;
    should provide the information. You should have recordNo as autonumber type and composite primary key (vehicleNo, odometer) in tblTripLog.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    IMHO, dangerous to rely on an autonumber field remaining sequential without gaps; in real life, they will usually develop a few.
    Paul

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you need to do this in a query?
    whats stopping you doing it on a report or form

    select your data in vehicle / date order

    then run some counters to derive your distance covered and calualte your fuel consumption

    doing it in a report would be a breeze.
    set a group header on vehicile ID, zero your control fields (eg date, previous mileage etc...) if you were running a date banded report put some SQL into the group header event to pull in the last know values before the date cutoff

    in the reports on detail event add calculate the report values (ie subtract current milage from previous milage, caluclate consumption, calcualte interval bewteen fillups [have a look at the date functions])

    you could even extend the report by using subtotals that give the total (& average)

    ...that should blow away teach.....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •