Moving from flat to relational

[sukisue]

I am working on a relational database to take the place of a (terrible) flat one. The original has almost 50,000 records. A lot of the data contains typos and mistakes which is one problem. I have populated my tables with accurate data but after the import of the old records into the new db- I will still have bad data. Other than going through each record- is there anything I can do to speed up the process?

Thanks

[Mike D.]

Depending on the type of data, you could export the old data to Excel then use editing tools such as find/replace and spell checking to clean up the data.

HTH, Mike

[BobWeaver]

There's a lot of manual work involved. But, I have learned a few tricks to make things go more quickly.

You can create some temporary calculation fields to flag suspect data. For example if you have a phone number field which should always have 10 characters, you could create a calc field with the formula:

Length(PhoneNumber)<>10

and then do a search for values of 1 in this field.

The WordCount function is useful for finding fields that have the wrong number of words in them. Eg., a field that should contain a person's first name should have a wordcount of 1.

You can get very creative with these kind of flag calculations.

Quite often, after isolating data with common errors (such as local phone numbers missing the area code) you can do multiple fixes at the same time using the replace command.

Another thing I do is use a list layout, and sort by the field that I want to check. Then, you can quickly scan through the list until you see bad data which will grouped together. This is a quick way to find misspelled place names etc.

[sukisue]

Thank you both for your help. I keep reminding myself it will all be worth it!