Show/Hide Toolbars

Ultra Mileage

Navigation: Examples: One2OneMileage

Example: Using One2OneMileage with a Microsoft Access Database

Scroll Prev Top Next More

This example shows you how to use One2OneMileage to compute the mileages for a Microsoft Access database. All routes are specified using longitude, latitude coordinates.

 

The example uses the routes table in the one2one_samples.accdb database that can be found in the Ultra Mileage examples file:

 

https://www.ultra-mileage.com/downloads/examples_ultra.zip

 

We only use the routes table, which has a design view that looks like this:

 

ex_one2one_routes_design

 

This has columns defining the source and destination coordinates, and four columns (distance, time, cost, and error) to receive the results. The error field is strongly recommended because this can be used to diagnose problems such as bad source/destination locations. The error field must be text, but the other result fields can be floating point (recommended) or text. A primary key is also required, and this can be either an integer or a text field. All primary key values must be unique, and the field should have an index. Here, we have called the primary key ID.

 

This is what the data actually looks like:

 

ex_one2one_routes_input

 

 

Setting the Ultra One2OneMileage Parameters

 

Start Ultra One2OneMileage. The parameters may be different, but you will be presented with a dialog box that looks like this:

 

ex_one2one_emptydlg

 

Press the "..." button in the Road Data box to select the Contiguous USA Fastest road pack (downloaded using the Download Manager).

 

Next press the Database Change button to display the Database Parameters dialog box. Set the database Type to "Access 2007 onwards (accdb)". Press the Data Source "..." button to select the Access ACCDB file that will be the data source. One2OneMileage will load this and scan it for the available tables.

 

Select the routes table from the list of available tables. This table contains our required routes and will store the results. One2OneMileage will scan the table and fill the remaining controls with the possible column options. Select the Locations are as "Coordinates" and press the Set Input Columns button to display the Input Locations dialog box. Set this to look like this:

 

ex_one2one_coords_inputlocs

 

This sets the primary key and the data fields that store the input coordinates. Press OK to return the Database Parameters dialog box, and then select the required output columns. You must set at least one of the output columns, otherwise One2OneMileage will not have any work to process. The resulting Database Parameters dialog box will look like this:

 

ex_one2one_routes_data

 

Press OK to return to the main dialog box.

 

Select the required routing parameters. Note that some of these options might be disabled if they are not required for your selected data. For example, the time units are not required if you have not selected route times. For this example we set the Distance Units to be Miles and the Time Units to Minutes. We also select Fastest Routes in the Find box.

 

Press the Rate Table Costs button to display the Costs and Rate Tables dialog box:

 

ex_one2one_ratetable

 

You can use this to define a rate table that applies a different cost according to the route distance. Here we simply apply a Constant Rate of 0.50 per mile.

 

Press OK to return to the main dialog box. This should now look like this:

 

ex_one2one_coords_dlg

 

 

Start Processing

 

Press Start to start processing. This is a very short database and should not take long to compute. The resulting Routes table should look something like this:

 

ex_one2one_coords_results

 

 

The exact numbers may vary according to the parameters that you have selected or the road pack version. The Cost field was created as a Currency type - hence the dollar signs.

 

Note that all rows were computed okay. One2OneMileage will add error notes in the Errors field if there were problems finding a location or calculating a route.