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