Database Editor Reply to Jim

Jim, 

I looked and DB2Query Mgr and SQL DevKit for AS/400 is not currently installed 
on our system (thank you so much for the info on checking that, it really 
helped).  I'm checking to see if it could be installed. Our O/S version is 4.5. 
 It's the Case Numbers that need to be changed, to match the new State of 
Florida mandated Case Number format.  Each client has a Master List number (the 
primary key) and under that ML#, the individual case information resides. There 
is a separate record created for each case and various tables record case 
information, payment information, community service hours completed, etc.  A 
client could have one case or twenty cases, depending upon how many times 
he/she has come into contact with the Criminal Justice system. We administer 
several programs including Pre-Trial Release, Pre-Trial Community Supervision, 
Mediation, Pre-Trial Diversion, Probation, Drug Court and Community Service.

Since we began using this software in '91, there has been a lot of "creativity" 
in regards to Case Numbers, not to mention the fact that the Clerk of Court has 
changed Case Number formats several times over the years as well.  The NEW 
format looks like this: 052003MM012345A.  The first two characters are the 
County Code (unique to each Florida county), the next four are the four-digit 
year, the next two characters are the Case Type code (could be MM for 
Misdemeanor, MO for Municipal Ordinance, etc.) then comes the 6-digit assigned 
case number, and the final character is a Defendant Identifier, usually an A, 
but there can be a defendant B, etc. when multiple defendants commit the same 
crime together (doesn't happen often with Misdemeanor cases).

When we first started using this software, every program had it's own method of 
assigning case numbers, so we have ALL kinds of creativity that happened in the 
past, and those numbers need to be converted to the standard format (above) 
that has been mandated for all Florida counties.  The old case numbers could 
look like 99-123456MMA (one of the old Probation formats) or 99-CV123456 (old 
Mediation format) or 01011957A (old Pre-Trial Release format that used the 
client's DOB for a case number because a Case Number had not yet been assigned) 
and so forth. We started using the new mandated format in January of 2000, and 
since then, have been working on converting all the old case numbers to the new 
format.

For $7,000, our software vendor created and ran some sort of program that 
converted approx. 90,000 numbers, but the remaining 20,000 were ones that had 
typos or mistakes, etc.  Somebody may have put a "/" where a "-" should have 
been or left out the "-" all together, etc.  Some of the old Probation cases 
have MM instead of MMA.  Some of the old Pre-Trial Release numbers had "/" or 
"-" and weren't supposed to have any special characters in them.  Anything odd 
or out of the ordinary in a Case Number kept it from being converted, which 
means that that case number now has to be converted manually.

As you can guess, it has been a MAJOR undertaking.  The thing is that even 
though the vendor could not convert those 20,000 numbers, I am able to see some 
consistencies among them - I can spot 10 or 20 or 50 that have the SAME mistake 
or difference and it would be a HUGE advantage to be able to convert those in a 
group somehow. The big problem is my inexperience.  I've never written a 
program and don't even know how to get started.  The AS/400 admin that we'd had 
since we bought the first system left the County last year.  I was just a 
regular clerical worker/secretary who was her emergency backup.  I knew how to 
change/initialize backup tapes, reset user passwords, restart printers and how 
to call IBM for emergencies - that made me the resident "expert" so the job was 
offered to me.  I've been struggling since then to keep my head above water and 
keep the system functioning. I have been a PC user for 12 years, but with no 
administrative responsibilities.  The AS/400 box was totally foreign to me, 
outside of being a simple user of the software. Our department totally subsists 
off of the Program Fees that clients pay, no General Funds are available to us. 
Therefore, no money is available for any type of training.  I've purchased 
books that I don't have time to read and even when I do read them, I don't 
understand much of what they are saying. I guess this places me up the 
proverbial creek without a paddling device!

Marsha Barton
Criminal Justice Services
1040 S. Florida Ave.
Rockledge, FL 32955
(321) 633-2006 Ext. 52330

Please make note of new email address:
marsha.barton@xxxxxxxxxxxxxxxx