Query question

[joolsUK0575]

Hi there

I have a database that has some data in it from an old database.

This data has been converted in to the new database (many years ago) and subsequently it has gone a bit astray.

Basically my problem is this.

My current database has 6 address fields in it. At the present time all newly inputted data is in the correct columns. But the old data that was converted in to the new database is all on one line.

There are some 12000 records with the address on one line. How can I split this data up without having to manually go through each of the 12000 records?

For instance. I need to be able to take out a town from the address and place it in to the correect column.

Any ideas or am I asking the impossible?

 

[Rich]

Are they comma separated?

 

[joolsUK0575]

Some of them are and some are not

 

[NoFrills]

So the old data is all on 1 line like?


(1 Ex) John Doe 1 Main Street New York New York 10029

(2 Ex) John Doe, 1 Main Street, New York, New York, 10029


If your lines are the (2 EX) then it is easy, if it is (1 Ex) then it is going to be difficult. I can think of a little trick in Visual Basic Code, do you have a version of Excel?

 

[joolsUK0575]

Hi No Frills

Yep. Both are examples of what I have in my database

I have Excel 2003.

I am reluctant to take the addresses out of the database as it is a LIVE database.

 

[NoFrills]

If you are worried about your database since it is live, if you have a backup database, use that. You can also wait to try this when you know the users will not be using it as it may slow the databse up until it finishes.


Have a Blank Excel Document Somewhere.

In Access:

To export data from access will not affect the actual data content of the tables, but depending on how many entries there are may slow your database it up some until it finishes exporting.

If you do not already have a Query Created with all the fields from your Address Table, create one. Simply create a query with the built in wizard, select the table with the addresses, dump them all into the query.

1 - Once it is create, simply Highlight.
2 - Go to File, Export
3 - Select "Save File Type: Microsoft Exel 97-2003
4 - Find that Blank Excel Document.
5 - Export

That excel document will now have all the data from that table.

Each column will have a field, so I have to assume the old addresses in your system are all within 1 column. The new data should be split within 6 fields.