break up a single field address

[Inspector]

I am a struggling low level user and any help will be appreciated. I’m trying to get data out of an old database and into a new one. The following addresses will give you some idea as to what my problem is.

I would like to break up the following single field address (Note the different pieces of data.
Washington Blvd 1407
Jefferson ST 234 N
Benjamin Ave 2105 N #1
Lincoln Cir 1407 N Unit 201 (Notice the space after Unit)

and have it look like this
Field one: 1421
Field two: N,S,E or W
Field three: BENJAMIN ST, Ave, or etc
Field four: Anything left over

If you can help, please talk very slow and loud cus I'm old and not too bright. I have limited experience with Instr, Mid, Left and Right functions, but can never seem to get them to work in a SQL statement I think I need to use multiple append queries but I’m guessing and then how do I get all the pieces into one table.

Thank you in advance

 

[The_Doc_Man]

Unfortunately you have picked one of the tougher problems to tackle. Parsing addresses is a frequent question for which there is no one good solution. In your case, the "correct" solution (don't store your data that way in the first place) doesn't seem to be available.

In an SQL query, I would have heartburn simply because there are limits to what you can do. Myself, I would write this operation as a RECORDSET operation via VBA, then write the parsing code myself. But that solution does not help you.

Search this forum for "Addresses" and "Parsing" as topics. You might get some insights. Otherwise I'm not sure where to send you because of your lack of more general DB experience.

 

[Inspector]

Thank you for the response. Through no fault of my own I inherited this mess. I had an Access book on my desk when the boss walked by. I have written some simple programs in VB several years ago so I do have a rusty recollection of calling up a recordset. If pointed in the right direction I might be able to fight my way through this.

Thanks again

 

[The_Doc_Man]

One thought that MIGHT help is to look for the spaces that delimit each element of your addresses.

Make a list of abbreviations for things like St. Ave. Blvd. Hwy. etc etc.

Isolate the elements. If you find an abbreviation and the previous item is either alphabetic or at worst alphanumeric, you might guess that it is a street-class item. Elm St., 23rd Ave., JFK Blvd., etc. There is an ISNUMERIC function that can help you recognize numbers, but if you get apartment 1A, you are probably hosed.

I would take a phased approach. Make a temp table of "addresses" to convert. Try to find what you can using the above scheme. Anything you CAN convert, do so. Remove the successful conversions from the temp and try again with a different set of rules. Whittle it down until you reach the point that you can the few remaining cases by hand.

Yes, I know it is tedious, but it is a really tough problem caused by the fact that street names are totally arbitrary. So there ARE no hard-and-fast rules for decomposition of same.

 

[raskew]

Doc_Man/Inspector,

Hate to break it to you, but you are hosed here. Short of moving it to an Excel spreadsheet and manually manipulating (cut/paste) the data line-by-line, this is not going to fly.

It appears that these are US addresses, written in a pseudo-European style format. Correct me if I'm wrong, but:

Jefferson ST 234 N -- translates to -- 234 N Jefferson St. - while
Lincoln Cir 1407 N Unit 201 -- translates to -- 1407 N Lincoln Cir., Unit 201

There are so many variations here, there's no way to programatically sort this out. Writing the code will take longer than debugging it manually. And, you'd still have to manually verify each record.

Inspector, my sympathies go with you. And, if Doc_Man can come up with a logical course of action, he's my hero.

Best wishes - Bob

 

[The_Doc_Man]

Bob, I have not tried to sugar-coat the nastiness of this problem. There is a rash of this going around right now. I've hit three or four "break up the address" questions in the last month. The only solution to this problem is to not store it this way in the first place. However, Inspector says it is already too late to store it correctly.

 

[raskew]

Doc_Man:

I absolutely agree with your observation. We've fought this stuff forever but, in the past, it seemed as if there was some simple logic to the posters' problems. Now it seems as if some folks are just 'bouncing off walls'

What's this world coming to?

Best Wishes - Bob

 

[neileg]

Sometimes the easist solution is to hire an experienced data entry operator and have then rekey the data into a new normalised database. Sounds inefficient, but the human brain still has the edge over the PC!

 

[SAMZIE]

read this, the answer is here http://www.access-programmers.co.uk/forums/showthread.php?t=105289&highlight=samzie