Hello All

I am updating our database so the address is split into 4 seperate address line fields. Currently the address is just in 1 field. We will be keeping this untill we have converted all the data into the 4 Address Fields. Does anyone know the best way to do this without disrupting any of the other data within the database.

Many Thanks

Steve

Don't know if i fully understand what you mean, but you want to split an address into 4 fields and store those fields in the database? Myabe use Left(),Mid() and Right() functions? Or is this answer too simple?

How many records are you talking about? I would be inclined to do this manually in Excel since there could be all sorts of problems with improperly entered and non-standard data (like "Maryland," "MD," etc.)

Now, if you need help with Find and Replace in Excel, or concatenation, we can do that.

The thing is, even if you have thousands of records, you are better off doing this ETL work manually since there is no way you could account for all the possible errors.

Otherwise your options include a large module or update/insert queries.

The way to start etl in Excel is to export the table into Excel. Ten add 3 additional columns, then copy and paste all the data from the fat address column into each of the three other columns. This will give you 4 address columns, all with the same data in them.

Then, do a find and replace one at a time like this:

In the fourth column, do a find for "* " (a space) and then a replace with "" (no value). Every time you run this it will delete the first word in the field. So if your address is "123 Any Street, Any Town, MD, 21043" the first time you run it it will drop off the street number, then the second time the first part of the street name etc. If you can be certain that all of your addresses have commas in the right places, then you can search for "*, " and replace with "", which will delete the entire street address.

Etc etc. I would want a real pair of eyes on this process though, and not a script or module. Maybe that's just me, but I have done this with 2500+ records at a sitting.

mmcdonal

A combo of both these suggestions may work if the data is consistently written. For example, if ALL addresses ALWAYS end in a five-digit ZIP code, then you know that Right(Address, 5) will always get you the ZIP code. And if ALL addresses end like this

"blah blah blah blah blah , NY 11220"

Then you know that Mid(Address, Len(Address) - 8, 2) will get you the state, etc.

But as mmcdonal warned, you MUST look at the data carefully to make sure that there's a pattern. If not, you'll need to do it manually with some tricks to make it easier, like the Excel technique.

Whatever you do, back up the untouched original data before you do anything!

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Blast! I forgot the data backup warning. Yes, always back up your data before you mess around with it.

mmcdonal

Since the question was about not disrupting any of the other data...

I'm going to assume that you have one address per ???contact???. But the setup I'll describe allows for multiple.

First, I would create another table in the database. The primary key to the new table should be an Autonumber field. (Note: Having a Primary Key that is autonumber in this new table will allow you to have multiple addresses for a single contact in the future.)

Your new table should also include a foreign key field. This field will link the address to the record in associated table. That is, this field will hold the value of the primary key to the record in the associated table (e.g. your Contacts table). If that associated table doesn't have a primary key, MAKE ONE!!!

While you are creating the table, add a foreign key field that will link to a lookup table that contains "Address types". For now, let that field default to zero. When you're ready to track multiple addresses, you'll let the user choose the address type. Create a unique index on the foreign key to your contacts table and the foreign key to the address type table. This will prevent adding two address of the same type.

For now, if you don't have multiple address and don't want to deal with that, just make the foreign key to the contacts table Indexed with no duplicates. This will prevent people from entering multiple addresses.

If the single address field is already in a table by itself and uses an autonumber field for the primary key and a foreign key to the contacts table, I would create another "join" table that contains a foreign key to the single address field table and a foreign key to the primary key of the new table. This will permit you to tie your converted data back to the original. This table isn't essential since you will already be able to tie the new table back to the old table by the foreign key to the contacts table. But if you have multiple addresses per contact, it will show you exactly which address was converted to the new address.

Finally, I would use a method similar to what mmcdonal suggests (using Excel) to split the address. When you extract the data for the split, be sure to include the Primary Key of the single address field table so that when you import the data to the new table you'll have the value for the foreign key of the new table. If your single address field is in a table by itself, include both the primary key and the foreign key of the single address table.

After the data is split as desired, use a Linked Table to the Excel file and run an append query from Excel to the new table. Be sure to include the key to your contact in your append to set up the foriegn key in the new table.

If the single address field is a table by itself, it's a bit tricky set up the "join" table that ties the new table to the old table. But you should be able to Join the Linked Excel file to the new address table by each of the address fields in the new table. This will give you the old address's primary key (from the Excel file) and the new table's primary key which you can append to the new "join" table. NOTE: if the linked Excel file has any fields defined as "Memo" you'll have to create a query that selects the fields from that table and join to that query instead of the Linked Excel file.

To prep your forms for data entry on this new address, just create a form that only updates the address. The recordsource for this form must include the foreign key to the contact table, but does not have to display that number. Add that form as a subform (child form) on any form where the single line address was updated. Link this Child form by the primary key on the "Master" form and the foreign key in the "Child" form.

If you set up the new table to be indexed no duplicates on the foriegn key to the contact, the child form will only accept one address per contact. (If someone tries to add a second address they will be informed that they are trying to add a duplicate key.) If you set up a unique index on the foreign key to the contact and the foreign key to the "Address Types" table, the child form will only accept one address per contact (because the foreign key to the "Address Types" table will always default to zero).

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org