# Thread: Query for date and mileage

1. Registered User
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!!

Marc

2. Registered User
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

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. Registered User
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:

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
Docmd.GotoRecord , , acNext
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:
in the second
in the third

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. Registered User
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 OdometerVariable as Single

Me.Form.Recalc
n=Dcount("[field_Date]","[Table Cars]")
Docmd.GotoRecord , , acLast
For i=1 to n
Docmd.GotoRecord , , acPrevious

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.

next and calculates the miles made for that interval
Else
End If
Docmd.GotoRecord , , acPrevious
Next i

Now i think it should work!!!

5. Registered User
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.

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

You will have: