Calculating Mileage

[Himself]

In my organization we have 15 buildings within a county that we have fixed distances between and offer mileage reimbersment. Our Technicians use a FM DB to track inventory and work orders on the inventory. Within this DB is a built in Scheduling system to assign the Technicians to a building for all or part of the day in instantances where the Technician travels between buildings they are entitled to mileage reimbersment. The Director of Technology now wants the FM DB to track the mileage for the Technicians since the information is already in the DB and produce a report for the Technician to sign off on.

Here is what I have to use:

Tbl_Appointments

Appointment_ID,Date,StartTime,EndTime,UserID,LocationID,Comment

Tbl_Mileage

LocationID_1,Distance,LocationID_2

How can I make a report where the calculation requires a lookup of a distance on a table using two records from another table?

The theoretical report would produce something like this:

|User Name

|Date 3/28/2011 Total Miles 6

| 1-AHS 0 Miles

| 2-AMS 4 Miles

| 3-CAO 2 Miles

|

|Date 3/29/2011 Total Miles 2

| 1-CAO 0 Miles

| 2-BMG 2 Miles

|

|Total Mileage for Date Range 8

Any thoughts?

Many thanks in advance for anyone who commits time to this problem.

[comment]

since the information is already in the DB

Is it? Looking at an appointment record - how do you know FROM where the technician (user?) traveled?

[Himself]

Is it? Looking at an appointment record - how do you know FROM where the technician (user?) traveled?

Based on the previous record. So for example:

1, 3/27/2011, 8:00 AM, 9:00 AM, Rob C, AHS, Install Stuff

2, 3/27/2011, 9:00 AM, 12:00 PM, Rob C, CAO, Meeting

3, 3/27/2011, 8:00 AM, 10:00 AM, Glen B, AMS, Alpha Project

4, 3/27/2011, 1:00 PM, 4:00 PM, Rob C, AMS, Alpha Project

5, 3/27/2011, 1:00 PM, 4:00 PM, Glen B, AMS, Alpha Project

6, 3/27/2011, 10:00 AM, 12:00 PM, Glen B, Out, Doctors Appointment

As you can see the records won't necessarily be created in the proper order but using the date and time stamps they can be sorted by Date,Person, StartTime. This is why the problem is a problem if the start location and destination were sitting on the same record it would be easy.

[comment]

I see. Well, the origin point could be looked up (or even calculated, if the amount of records is not too large) using a self-join relationship of the Appointments table where:

Appointments::UserID = Appointments 2::UserID

AND

Appointments::Date = Appointments 2::Date

AND

Appointments::StartTime > Appointments 2::StartTime

Sort the related records from Appointments 2 by StartTime, descending - so that the first related record is the previous appointment of the user on that day.

[Himself]

I see. Well, the origin point could be looked up (or even calculated, if the amount of records is not too large) using a self-join relationship of the

I wouldn't expect many records per user per day. Ideally they attempt to schedule a Technician to be in one building for an entire day but life is not always that simple and the need to move Technicians around always presents itself. I would say the max might be 4 locations in a day but the majority will be 1 or 2 locations in a day.

I had come up with using a calculation field in the Appointment table to identify the destination location. That would then link to the Mileage table nicely. The calculation in that field was my next step so I will look into your suggestion tomorrow since my day is now over.

Thanks for you time and consideration.

[Himself]

It works! Thanks again for the push in the right direction.