Post office zip and address changes to my DB

[Maxwell Morlay]

The post office has corrected all my zips and addresses (CASS Certification). I have these in a simple DB with name, address, city, state, zip. Now I need to get all these changes back into my original DB. Not every record has changes; only about 10% out of 8,000 names.

How do I get those changes back into the correct records in my original DB? Those corrections will be made in the "address," "zip" and "zip+4" fields. Thanks so much.

Maxwell Morlay

[CobaltSky]

If you are able to assemble a unique key field to match in both the original file and the corrections db, then there are a couple of procedures you could consider. Here's one:

Back-up the original database before you start.

Once you have created your unique key fields (probably via a calculation which concatenates name and other elements from the available fields) in both databases, set up a relationship within your original file which links to the corrections db via the unique key fields.

Next create stored calculation fields with formulae along the lines of:

Case(IsValid(CorrectionDB::Zip), CorrectionDB::Zip, OriginalZip)

for each field with corrections.

Now browse through the file to confirm that the calc fields are picking up the corrections (but displaying the original data when there is no record in the correction file. If all is well at this point:

1. Enter define fields and change the stored calculation fields to stored data fields (text or number, to correspond to the original data fields).

2. Change your original data fields to stored calculations which reference the new fields which are now data fields (exit from define fields to force storage of the calculation results and confirm that all is well.

3. Re-enter define fields and change this second set of stored calculation fields to stored data fields (text or number, as before).

4. Delete the fields which were used to bring the data across from the corrections DB.

Once complete, you should consider saving the file as 'Compressed copy (smaller)' to remove unused space, and you should then be 'in business'.

Although the above procedure is a little more involved than a look-up based approach, it affords better options for error detection and correction (because you get to compare old and new data side by side before proceeding with the change...).