Importing data from Access to Access(in a bad format)

[dcjones]

Hi All.

I am developing a new database for my employer. The database is near completion. One of the problems I now have is to get the current data into the new tables.

The curren t database was designed (NOT) by someone who had never NOT designed a system before, and should never try again. The main table contain details of clients with fields such as:

custID
custname
custaddress

(and so on )

It's the custaddress field that is presenting me with a problem. The whole address is in one field i.e "10 downing street westminster london w1 1nb"

The new database also had a table containing details of clients with fields:

CustID
CustName
CustAddressFirstLine
CustAddressSecondLine
CustAddressTown
CustAddressCounty
CustAddressPostCode.

QUESTION: Is there a way of extracting and importing the data from the current database-field into the new structure placing the correct parts of the address in the fields as listed above. So far the only way I have found is to export the table into Excel, manually separate the individual parts of the address and paste them into cells on the spreadsheet and then import them into the new table.

PROBLEM: there are over 3500 records the the current table.

Any advise would be good, (Apart form GIVE UP)

Kind regards, keep safe and well.

Dereck

 

[spacepro]

Have a look at this thread :

http://www.access-programmers.co.uk/forums/showthread.php?t=34942&highlight=seperate+address+field

or do a search for 'seperate address'

Andy

 

[dcjones]

Hi Andy,

Thanks for your reply. I have looked at the other post and completed the first part and produced a table with the house numbers. What I don't understand is the query I am useing, see below:

What do I need to change to get the secoend part of the address and so on.

OK here’s how you do it:

Make a Make Table query and create a special field (call it StNumber. . .or whatever you want). The add some code as I have below:

So. . .

StNumber: IIf(Int(Left(trim([Address]),2))>0,Left([Address],InStr([address]," ")-1),"Doesn't matter")

When you view this query all the street numbers will appear but the places where there is text will give you an error. . .this is fine. You will also need PK field of some sort.

When you run the Make table query, it will give you a table of all the numbers, but will put in null values in the areas where there are none.

Once you have this you can probably think of how to get the rest. . .I would take that table link it back up to the address table via the PK and then make a new make table query with a both the fields you want. You have the first field already StNumber and the second field is easy just do

Street Name: right(address, len(address)-len(StNumber)). . .or something close to that. . . I forget if the length of a null field is zero or not. . .if it isn’t you’ll have to use an iif to do something with the null values in StNumber

It would be a problem if the NUmber is like a234 whatever street, but other than that you should be OK. . .

The reason I use Left(address,2) is for things like 1st street and such. . .

Regards

Dereck

 

[spacepro]

Hi Dereck,
Have you tried this :

Right([streetname],Len([streetname])-InStr([streetname]," "))

Hope this Helps

Regards

Andy