Conversions are not for the faint of heart. If you are doing the conversion, you should have the Error Checking List and the So You Want To Convert article by Kemp's Case Works. You should also have a working knowledge of Access 97 or the willingness to learn as you go. The queries provided do not do everything and are no substitute for common sense. They will take you within 90% of completion. Some queries can be slightly modified for use on other fields, with minor modifications. For example, the query to convert the case (upper to lower) of the client's last name in the ClientsW table (FBConvertClientsWLName) could be easily modified to correct the case for any other fields that were entered in all caps.

The conversion steps are laid out in order from 1 through 8. Most of them have a set of associated queries.

IMPORTANT! BEFORE DOING ANYTHING, BACK UP YOUR COPY OF TURBO CASES SINCE WE WILL BE MODIFYING THE DATA. IF YOU CREATE A NEW COPY ON YOUR HARD DISK IN A DIFFERENT FOLDER, YOU CAN THEN WORK WITH THE COPY RATHER THAN THE ORIGINAL.

STEP 1: Open the ClientsT981.MDB file (the one set up to convert Turbo Cases) using the full version of Access 97. The first thing to do is make this file aware of the Turbo Cases data files. From the Top Line Menu choose File, Get External Data, and Link. Using the supplied dialog box, change the type of files to attach to dBASE III files. Go to the folder that contains the Turbo Cases data files. Point to the first file ending with .DBF and press the Link button. When asked for an Index, choose Cancel. Repeat this process for each .DBF file in the folder.

STEP 2: Using the Clients 98 Manual and your knowledge of the Turbo Cases Codes used, go into the section dealing with setting up Clients for Windows. Instead of using the Upkeep Tab, double click on the table name to open it. For example, to add funding codes double click on the subFunds table. Repeat for offices, etc. Be sure to read below, since I can do some of the work for you. If you have more than 9 offices, you should skip the number 10 and go to 11. This is because it will be easier to tell the office location from the case number, e.g., 98E-1100006 is easier than 98E-1000006 (is this office 1 or office 10, hmm, lets count the number of characters).

BEFORE RUNNING ANY QUERIES, OPEN THEM UP WITH THE DESIGN BUTTON AND BE SURE YOU UNDERSTAND WHAT THEY DO!

STEP 3: Examine and use the Queries beginning with A. Several of these queries have to be modified for the proper location and some need policy decisions before being run. For example, do you want all the cities in California to display or only the cities in your counties? Which Zip Codes do you want on your pull down?

Another important procedure is to merge your archive data with your full data. You do not want to repeat your error checking for two, three or more tables. Besides, you would have to modify all the queries I gave you to work on the different tables that would result. An example query that shows you how to Append the Archive file to the CSR table is called AEQAppendArchivetoCSR. Another procedure is to move your staff members from the Lawyers1 table to the Smember table in two steps. Once you moved them into your _TempSmember table you need to modify the design of the table so the Snum field is no longer Autonumber but a Long Integer. Once you do this, you can type in the number of each staff member.

THIS IS VERY IMPORTANT WHEN YOU HAVE MULTIPLE OFFICES! EACH STAFF NUMBER MUST BE UNIQUE. YOU MAY WANT TO USE THE OFFICE NUMBER FOR EACH OFFICE FOR THE 100 SERIES STAFF MEMBER. FOR EXAMPLE, LAWYERS FROM OFFICE 2 MIGHT HAVE SERIES 200 NUMBERS, E.G., 201, 202, 203, ETC.

Double click on the tables that begin with the underline mark. These are conversion tables that convert the Turbo codes to the Clients for Windows (CFW) data. Check these conversions or add or subtract from them. The _TempTime and _TempSmember fields need to be empty when you start, since certain queries will modify them, e.g., Turbo to Temp to final CFW table.

STEP 4: Examine and use the queries beginning with B. Open each Turbo table by double clicking on it on the Table tab. Click on a value under the first column. Then press the A to Z button on the top line. This sorts the database on that column. Many errors will occur at the beginning or end of the database. Look at the first records and correct any errors. Then sort from Z to A. Correct any additional errors. Repeat this process for each column in each Turbo table.

Next run the queries beginning with B which will either find or correct errors in your tables. In some of these queries, the result is a grouped list with numbers of the occurrence. This can help you determine if you have improper values in the list. For example, if you run the Branch version and should only have values from one location, other values will have to be changed using an Update query or by individually editing each incorrect value.

STEP 5: Examine and use the queries beginning with C. This set of queries goes into the Turbo tables and actually changes the data before it is moved into the CFW tables. Many of these queries depend on the tables beginning with an underline for the conversion data, so first make sure these tables are set up correctly. Certain queries, such as the one that removes blanks from the front of the clients first name, can also be modified to work with other fields, e.g., case number, last name.

STEP 6: Examine and use the queries beginning with E. These queries actually map the Turbo data to CFW tables. Look at them and make sure they capture all the data you need. Some Turbo cases fields were omitted where there were no corresponding CFW fields. In some instances, such as time keeping, the records are grouped together by case number and advocate and added together. For example, if case number 96-10202 had 43 time records entered by advocate 2 that totaled 56.3 hours, only a single record would be entered into CFW Ttime table with 56.3 hours and a note saying it is a balance from previous time keeping.

Turbo cases was sometimes used to store organization and government adverse party names. Instead of putting all of these in the last name adverse party field, some users put part of the name in the first name adverse party field, e.g., fnadv: Joe's lnadv: Auto Parts; rather than fnadv: lnadv: Joe's Auto Parts. These queries attempt to recognize this and move the data from the first name to the last. You can add additional queries like this using words like police and housing.

When running conversions, note the number of records sent and the number that were successfully transferred. Key violations can prevent all or some of the records from moving over. These are fields in the data that lack a value or have an improper value. You can see a query that shows how to find records that didn't move over by looking at EKQMissingCSRs. Notice how the records are linked by a field that is unique. Go column by column and see if these records all share a value, such as funding code 44. Then check an see if funding code 44 is in your subFunds setup table.

STEP 7: Examine and use all the queries beginning with F. These queries clean up the data once it is placed in CFW. The ones provided change the case so that data uses upper and lower case.

STEP 8: Highlight each Turbo table and tap the Delete key. Examine each of the CFW tables and make sure the values transferred over correctly. If they did you can delete the connection to the old Turbo table. This does not erase the table but merely deletes the link to the table, so that ClientsT981.MDB is no longer aware it exists.

This should conclude your conversion.

Note: I was unable to work with any memo fields, nor was I able to map the status field. I did not do anything with PBI cases, since I could not identify them.