TECHNICAL ARTICLE: howto_012.htm

 Close Window  


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.

SQL Statement
SELECT 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 Maintenance.NO_ORDER


For each duplicate work order number found, you will see the Order No, VIN, and Veh No as 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.

Sample Results
Query Start - 10/16/2012 2:39:56 PM
Rec:0000001 - FM-1000191 67890123456789012 555-5
Rec:0000002 -
FM-1000191 34567890123456789 121-12
Rec:0000003 -
FM-1000191 67890123456789012 555-5
Query End - 10/16/2012 2:39:59 PM


If you need additional assistance, please write or call us: 

FLEETMATE Software Copyright (c) SCB Consulting.  All rights reserved.
FLEETMATE is a registered trademark of SCB Consulting, LLC.