HOW TO: Locate VINs with Duplicate Work Order Numbers
In order to migrate FLEETMATE JET data to
a FLEETMATE SQL Server database, you will need to ensure that there are no duplicate work order numbers
in your existing JET database. The migration utility will not
migrate data if it locates any duplicate work order numbers. This document will
help you locate the asset records where duplicate work order numbers are found.
Start FLEETMATE to access your existing database. Use the File
| Database | Issue
SQL Statement... menu option to open the Issue SQL Statement
dialog. Copy and paste the SQL Statement below into the center pane of the Issue
SQL Statement dialog. Make sure that the Query Type is set to Select,
then click the Execute! button for your
results. With a very large database, this query may take several seconds to run.
Maintenance.NO_ORDER, Maintenance.TX_VIN, Vehicle.TX_VEHNUMBER FROM Vehicle RIGHT JOIN Maintenance ON Vehicle.TX_VIN = Maintenance.TX_VIN WHERE (((Maintenance.NO_ORDER) In (SELECT [NO_ORDER] FROM [Maintenance] As Tmp GROUP BY [NO_ORDER] HAVING Count(*)>1 ))) ORDER BY
For each duplicate work order number found, you will see the Order No,
VIN, and Veh No
illustrated in the Sample Results below. You can copy/paste the results
into another application, such as NotePad so that you can print the results.
Using the Veh No and VIN values in the results, you will be able to select the appropriate
asset records, locate the work orders, and edit each duplicate work order
number, making each order number unique.
In the Sample Results below, order number FM-1000191
was found three (3) times, two times for VIN 67890123456789012
and once for VIN 34567890123456789.
Start - 10/16/2012 2:39:56 PM
Rec:0000001 - FM-1000191
Rec:0000002 - FM-1000191
Rec:0000003 - FM-1000191
Query End - 10/16/2012 2:39:59 PM