Unanswered: Seperating Street Name and Street Number from Address Field
I have trouble figuring out how to seperate an Address field in a query. The Address column contains street number and street name and I have to seperate them into different columns (street number column and street name column) so that I can differentiate records which have the same first name and last name by their address. I was told that I need to write scripts on the query grid under Field row but I could not figure it out.
I would suggest looking into getting some third party software. Determining which part of the address is the house number or the street name can be very complicated. There are companies that spend alot of time and money proving to the U.S. Post Office that they can accurately determine this. It's called CASS certification. The other benefit to buying their software is they typically come with a way to determine if there are duplicates within a list, which is what I am assuming you are trying to do.
I use a couple of products from these companies that are somewhat reasonable, Accuzip and The Software Company. I would reccommend the ActiveX control from The Software Company. It will split addresses for you, but you do need to know how to program to be able to use it.
The reason I wanted to do this because I am working with PDI Database and there are hundred of thousands of voters in one city. Imagine if I have to work with a few cities, I would have a bunch of people who has the same last name and first name. In order to match them more precisely, I have to match their addresses as well. Sometimes, I have some records with the same last name, first name and street name so I have to match them with street number.
I agree with DCKunkle's comment, and my job IS processing address databases containing 100,000's or records on a daily basis for the US and Canada Postal systems. M4rl1n may consider having the lists processed by companies specializing in this field, (ours included ). The cost of the software is too prohibitive for temporary purchase. In addition to CASS coding, there is also NCOA (National Change Of Address) processing by companies licenced by the USPS and Canada Post. You (M4rl1n) should also consider the fact that street address segments are more than just numbers.