Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Posts
    3

    Unanswered: Query for date and mileage

    I am trying to compute the mileage put on a vehicle during a month. The table the data is stored in has the following fields: License Number (license plate of each vehicle), Date (day of the month the data is entered, usually the first day of the month following the month where the miles where actually put on the car, e.g., 4/1/2002 would be the date the mileage for the month of March was entered), and Odometer (the odometer reading on the last day of the month, before it was sent to the office). What I need to compute is the number of miles put on a vehicle for each month over the last six months.

    Because the Odometer field is the actual odometer reading at the end of each month, I need to take the reading from 4/1/2002 and subtract the reading from 3/1/2002, and repeat this for the six month time span prior to the current date. I am not sure of how to do this? Do I need a Loop statement? A crosstab query? I am really struggling to conceptualize how this can be done. Any help would be extremely appreciated!!

    Thanks in advance!

    Marc

  2. #2
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    Perhaps you can consider inputting both the start and the end mileage..
    If you do, a form can greatly help the person inputting.
    If you don't want to do this, you can do something like this:

    SELECT tblMiles.License, tblMiles.Date, tblMiles.MilesOnReturn, [tblMiles].[MilesOnReturn]-[tblMiles_1].[MilesOnReturn] AS MonthlyMiles
    FROM tblMiles INNER JOIN tblMiles AS tblMiles_1 ON tblMiles.License = tblMiles_1.License
    WHERE (((Month([tblMiles_1].[Date]))=Month(DateAdd("m",-1,[tblMiles].[Date]))))
    ORDER BY tblMiles.License, tblMiles.Date;

    This only works if you enforce that input of miles is done only once a month, because the query uses 'compare this month's mileage to the mileage of thismonth-1" logic.

    Other solutions/suggestions/input welcome...

    Hope this helps...

  3. #3
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Mackermj,

    If i understand your problem, you need to calculate the miles made in the car since the last record that you have in the database. Well, if there is, you can use the function Dlast() to solve your problem, use it like this:

    On the odometer control AfterUpdate event, type this code:

    Dim LastReading as Single

    LastReading=Dlast("[Odometer]","[Table cars]","[License Number]=Forms![Your form's name].[Control name of license numb.]")

    [miles made this month]=me![Odometer]-LastReading

    If you are inputing data monthly than for the same cars. But if you're inputing data, 6 in 6 months, then divide by 6.
    Now if you have the data, and now you want to calculate, then, create a button, and type a code like this:

    Dim n as Long
    Dim i as Long
    Dim OdometerVariable as Single

    Me.Form.Recalc
    n=Dcount("[field_Date]","[Table Cars]")
    Docmd.GotoRecord , , acLast
    For i=1 to n
    Docmd.GotoRecord , , acPrevious
    OdometerVariable=Me!Odometer ' Reads the previous Odometer reading
    Docmd.GotoRecord , , acNext
    [Miles made this month]=Me!Odometer-OdometerVariable ' Reads the next and calculates the miles made for that interval
    Docmd.GotoRecord , , acPrevious
    Next i

    Notes: Before running this code, you should order the query that the form is based on, and i say query because when a form is based on a table, you have to construct a query in the form to set the order type you want. Ok just say you have a query, in the form goto Data Source or Object Source, just click for form propertys, and in the data tab click in the first option. Open the query in there, and set order by license and order by date. So that you can have the records ordered for calculation, or else it would calculate wrong values, for example:

    in the first record you have:
    license=111
    in the second
    license=222
    in the third
    license=111

    if the records haven't been sorted by order the diference betwen record tree and record two would be wrong, because their are two diferent licenses.

  4. #4
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    mackermj,

    You're welcome!!

    The calculation button it's only doing by date because, in the last code i do not put an if statment, that is when the license change, because there are diferent cars and when the the record goes back it will pass from a license "a" to a license "b" and the odometer should be zero because there are diferent cars.

    Try this:

    Dim n as Long
    Dim i as Long
    Dim LicenseComparison as String
    Dim OdometerVariable as Single

    Me.Form.Recalc
    n=Dcount("[field_Date]","[Table Cars]")
    Docmd.GotoRecord , , acLast
    For i=1 to n
    Docmd.GotoRecord , , acPrevious
    LicenseComparison=Me![License_Field]
    OdometerVariable=Me!Odometer ' Reads the previous Odometer reading

    Docmd.GotoRecord , , acNext
    If Me![License_Field]=LicenseComparison then 'Compares if it is the same license number, if not, then it means that is the first record inputed in the database for that car and so the odometer should be the first reading or null.

    [Miles made this month]=Me!Odometer-OdometerVariable ' Reads the
    next and calculates the miles made for that interval
    Else
    [Miles made this month]=InputBox("Enter the first reading for the car which license is " & Me![License_field] & ", orresponding to the date: " & Me![Date_of_reading_field], 0)
    End If
    Docmd.GotoRecord , , acPrevious
    Next i


    Now i think it should work!!!

  5. #5
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Mackermj,


    I forgot to tell you that you should group the records, just like:

    Group by License, Group by date, group by...

    Without any sum or average, etc.... Just Group all records, and sort them by license and date.

    Now instead of having:

    License Date
    111 10/05/2002
    112 11/05/2002
    111 12/05/2002

    You will have:

    License Date
    111 10/05/2002
    111 12/05/2002
    112 11/05/2002

    And so on.

Posting Permissions

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