Caclulating miles for leg of a trip 1

[Tsumara]

I have a report that displays trip summary information based on a user input date range. In the report detail I display information for each stop on the trip. At each stop on the trip I have an odometer reading. There can be several trips on one report.

I want to display the number of miles between each stop.
I am having problems setting a field equal to the current odometer minus the previous odometer.

 

[dhookom]

You can calculate this in a query using either a subquery or DLookup() or DMax() to get the previous odometer reading.

If you had some table and field names as well as sample records, this would help someone provide a specific answer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tsumara]

Ok, Sorry about the untimely reply.
I have two tables tblTrip and tblPickups

TblTrip contains
TripID, StartDate, EndDate, StartOdometer, EndOdometer,...

tblPickups Contains
PickupID,TripID(forgin key), Odometer,CustomerID, InvoiceID

I want to show the miles between each pickup.

 

[dhookom]

This SQL should get you the current and previous odometer for each "leg" of the trip.

Select tblPickups.*,
DMax("Odometer","tblPickups","TripID = " & [TripID] & " AND Odometer<" & [Ododmeter]) as PrevOdometer
FROM tblPickups;

This assumes TripID and Odometer are both numeric which is only a guess since you neglected "as well as sample records".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tsumara]

Wow Thanks dhookom. That works like a charm.
I'm glad it is only for a report, this query really bogs my machine down.