I am guessing, once the bad number is written somewhere, it gets reentered again (documents, etc).
1. Create a new table within your current database design called Bad-Cust-Num. 2. Create a display / edit screen to populate the Bad-Cust-Num database. 3. Add a function to this screen to change 1 Cust-Num from the Bad value to the Good value. This transaction could run a while, depending on how many records it has to update. 4. Modify your data entry program. When they enter a Cust-Num, Inquire to the Bad-Cust-Num database. If found, utilize the correct number instead. 5. Modify your inquiry program to query the Bad-Cust-Num database and respond with the Good-Cust-Num. Possibly, a better name would be the Old-Cust-Num database? Just use the same program in case you need to change a legitimate customer number for some reason. I. E. two customers who keep using the same number when they call in. On Tue, Nov 20, 2012 at 2:41 PM, Joel C. Ewing <[email protected]> wrote: > On 11/20/2012 10:57 AM, Ron Thomas wrote: >> >> Hello. >> >> We have a input file with the below format >> >> Date Bad Custno Good Cust No >> 01/01/2012 989898 12345 >> 02/02/2012 12346567 9898790 >> 03/04/2012 676767 767689 >> >> This file is a sequentail file wil get appended on a daly basis , i.e >> they will be inputting a bad customer number and a good customer number. The >> customer numbers are used in around 20 tables and because of some typo >> errors or any other thing the bad customer numbers got entered in to the >> tables. >> >> We need to correct the same on a daily or weekly process. could some one >> throws some light on what should be the best process we should consider >> here. The customer numbers are primary keys in many of the tables. >> >> Thanks in Advance >> >> Regards >> Ron T >> >> > What is the logic that detects that the customer number is "bad"? The best > solution of course would be to apply that logic at original data entry > process, detect that the value is "bad" and force whoever is doing the > original data entry to supply a valid number before it is saved in any > tables - better yet include cross checks in the process to also minimize > the possibility that the number is valid but belongs to a different > customer. > > As others have pointed out, a simple daily substitution list won't do much > good in fixing mis-entered data in your tables if the "bad" value is valid, > but for a different customer than the one intended. If the customer whose > customer number was misappropriated also has transactions on the same day, > how is a simple daily substitution list going to distinguish between valid > and invalid entries for that customer number? > > -- > Joel C. Ewing, Bentonville, AR [email protected] > > ---------------------------------------------------------------------- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to [email protected] with the message: INFO IBM-MAIN -- Mike A Schwab, Springfield IL USA Where do Forest Rangers go to get away from it all? ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [email protected] with the message: INFO IBM-MAIN
