Show/Hide Toolbars

MPMileage for Maptitude

Navigation: Examples

Example: Using MPMileage with a Microsoft Access Database

Scroll Prev Top Next More

This example shows you how to use MPMileage to compute the mileages for a Microsoft Access database based on the sample database 'Clients.mdb' from Microsoft. The example requires Microsoft Access and Caliper Maptitude (2014 Build 2835 or later) with the US Country Pack.

 

The example uses the sample_clients.mdb database found in the MPMileage for Maptitude samples file:

 

http://www.mpmileage.com/downloads/examples_mpmileage_mpt.zip

 

The database file sample_clients.mdb contains two tables: AddressesTable and Mileages. AddressesTable stores the client names and their locations. It is not directly required by MPMileage. Instead you must import it into Maptitude to create a new data layer. The database looks like this:

 

sample_clients_db

 

Import this database into Maptitude using Maptitude's Create-a-Map Wizard (or import and add as a new layer). The wizard calls the new layer 'My Data (Addressesta', so we have renamed it to "clients".  It will look something like this:  (clients are marked with blue stars, and streets have been hidden for clarity):

 

sample_map

 

The second table in the database is the Mileages table. This is used directly by MPMileage, and lists the required start and end locations. It will also hold the results. This is what the empty Mileages table looks like:

 

sample_mdb_empty

 

Each row specifies one route mileage to calculate. The ID field is not used by MPMileage and will be ignored. The Source and Destination fields refer to the required start and end pushpins in the above map file. The Distance, Time, Cost, and Errors fields will hold the calculated results. The Errors field is defined as text. The other three fields can be floating point numeric (recommended) or text.

 

 

Setting the MPMileage Parameters

 

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

 

main_panel_start

 

Press the Database Change button to display the Database Parameters dialog box. Set the database Type to "Access 1997-2003 (mdb)". Press the Data Source "..." button to select the Access MDB file that will be the data source. MPMileage will load this and scan it for the available tables.

 

Select the Mileages table from the list of available tables. This table contains our required routes and will store our results. MPMileage will scan the table and fill the remaining controls with the possible column options. Select the Locations as "Named Data Points" and select the dataset containing the points (renamed clients above). We will set the columns that hold the start and end locations below. You should also select the dataset's field (Name Field) that contains the identifying names for these points. These are the names used in your route database to identify the start and end locations. Here it is called Clients.[Contact Name].

 

Then select the required output columns. You must set at least one of the output columns, otherwise MPMileage will not have any work to process. The resulting Database Parameters dialog box will look like this:

 

db_panel

 

The start and end locations in the Mileages table refer to named locations in the new clients layer. These are selected in the Input columns box. The layer's name field also has to be selected (Clients.[Contact Name] in this example).

 

Next, set the data fields for the start and end locations (i.e. data points). These are set by pressing the Set Columns button in the Input columns box. This displays various location drop-down boxes that are used to set the start and end locations. This example works with pushpins, so there are only two drop-down boxes displayed. Set them as follows:

 

input_locs_pushpins

 

This dialog box will have more drop-down boxes if you have selected street addresses or coordinates for the location types. For example, street addresses require drop-down boxes for streets, cities, zipcodes/postcodes, and country.

 

Press OK to return to the Database Parameters dialog box, and 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. Press the Road Speeds button if you wish to modify the road speeds. Press the Costs button to edit the costs. You can specify a fill rate table that is based on route distance. We will just set a constant rate of $0.10/mile:

 

rate_constant

 

 

Start Processing

 

Once everything has been set, return to the main panel and press Start to start processing. This is a very short database and should not take long to compute. The resulting Mileages table should look something like this:

 

sample_cleints_results

 

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

 

Note that all but two rows were calculated okay. John Kelly could not be found in the Clients layer, so MPMileage was unable to calculate the two routes that referred to this location.