Jump to content

Calculating Mileage


Himself
 Share

This topic is 4332 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4332 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.