How to divide the address field into smaller parts

Can you pinpoint a definite pattern in splitting up the addresses?  The pitfall in automating something like this is:

>38 Baja Harris fountain Rd  -- Should this be
field 1                 Field 2
------------------------------
38 Baja              Harris Fountain Rd   ?
38 Baja Harris     Fountain Rd            ??

How do you define the split so that it can consistently work for this and all other entries?

It should be

Field1                          field2

38 Baja Harris             Fountain Rd

What I think I should do is divide all the spaces to a seprate field and then somehow join the wanted ones back together.
BackGround : (Reason Why I am doing it):

I have two tables.

table1
ID
Name
Address

table2
Name
Address
Phone Number

Now table 2 I had imported from another data base and table 2 contains the phone number for some of the records in table 1. I want to match the table 2's address field to table 1's address field. But format is different. If I get them exactly same then I can do a query search to find the phone numbers from table2 and enter them in table1 where the address is same.

However, like always I have a problem here again.

The format of address in table 1 is like this

300 bridge creek road

and in table 2 they are using short names and stuff
like this

300 bridge creek Rd

or it could be

300 bridgecreek rd

now how can I solve this If you have a better idea I am willing to try it.

Thanks

Are the Name fields similar?

yea the name field is same. But the problem is that there are more than one similar names you know and I don't want to use name to merge it with phones from table cause than data will not be correct. I want to use name and address for the search of phone number. so if the name and address from table 1 matches table 2, then thats the phone number in table2 that we want in table1. Hope I explained it ok
Thanks

Okay... How many records are we talking about?  A thought I had was to use the Name field and the first few characters (possibly the numeric portion) of the address field.  I would think that would give you the vast majority in a relatively simple way.  It would be easier than dividing the field and possibly more reliable...

Take a look at http://www.thenelson.name/#Set_UpperCase_Demo.  You can use it to program Road, road, ROAD to Rd, etc.  Use the routines to compare or in an updated query to change one of the tables then compare.

You might also be able to use soundex to do the comparison.  http://www.j-walk.com/ss/excel/tips/tip77.htm

Mbizup

 >>>>>A thought I had was to use the Name field and the first few characters (possibly the numeric portion) of the address field.

Thats a good idea. I was thinking about it too but I was not sure how to really do it

Give this a shot...

This is an action query that will modify your table so please Make a Backup!

Add a field for the phone number to table1.
Make a new query with this as its SQL:

Update Table1 inner join table2 on Left(table1.addr, InStr(table1.addr, " ") - 1) = Left(table2.address, InStr(table2.address, " ") - 1) AND table1.Name = Table2.Name
SET table1.PhoneNumber = Table2.PhoneNumber

This will join the two tables where the Names and everything up to the first space in the address field match, and set the phone number field in table 1 equal to the phone number field in table2

I changed table1.addr to table1.address as in your question, but do Check the otherfield names in the query I posted to make sure they match your application's field names.  

Update Table1 inner join table2 on Left(table1.address, InStr(table1.address, " ") - 1) = Left(table2.address, InStr(table2.address, " ") - 1) AND table1.Name = Table2.Name
SET table1.PhoneNumber = Table2.PhoneNumber

mbizup

Thank you for your code. WOuld it be possible to try to match the address up to the second space and not only the first space.
What I mean is this

Carlos        3456 Montview park dr          

and if it can match 3456 Montview  instead of only 3456

thanks

Any idea???

Thanks

Sorry it's taken me so long to respond...

add this to a module:

Public Function FirstTwoParts(s As String) As String
    Dim arr As Variant    
    arr = Split(s, " ")
    FirstTwoParts = arr(0) & arr(1)
End Function

then modify the query like this:

Update Table1 inner join table2 on FirstTwoParts(table1.Address) = FirstTwoParts(table2.Address) AND table1.Name = Table2.Name
SET table1.PhoneNumber = Table2.PhoneNumber

This should compare the first tow words in the addressfield in both tables.  Make a backup and give it a try.

it gives me a Debug error

"Subscript out of range"

and highlights this

FirstTwoParts = arr(0) & arr(1)

Thanks

When I tried it again, it stopped with a warnning of "DATA MISMATCH CRITERIA"

Thanks

Could it be because PHONE field in table1 is indexed and in table2 it is not?..Same with Name fields.

Thanks

I did indexing. It still hangs up after 2 minutes with this warning

"Data type mismatch in criteria expression"

thanks

I think the first error might be because of blank addresses, or addresses with no spaces.  try this:

Public Function FirstTwoParts(s As String) As String
    Dim arr As Variant
    if nz(s,"") = ""  then
         FirstTwoParts = ""
         exit function
    end if
    arr = Split(s, " ")
    FirstTwoParts = arr(0) & arr(1)
End Function

I tried
Public Function FirstTwoParts(s As String) As String
    Dim arr As Variant
    if nz(s,"") = ""  then
         FirstTwoParts = ""
         exit function
    end if
    arr = Split(s, " ")
    FirstTwoParts = arr(0) & arr(1)
End Function

 and it still is giving this error

"Data type mismatch in criteria expression"

Thanks

I went ahead and deleted 5479 records with empty address field in table2. Now it gives me this error

"Subscript out of range"

and highlights this

FirstTwoParts = arr(0) & arr(1)

Thanks

It also says in the warning box,
Runtime Error 9

I thought may be you need this info

Thanks

When I use the above code,

The program just keep running and nothing happens and I have to restart computer. It doesn't hangup but it looks like its not doing anything in access window and if I try to go to anyother program and want to get back to access window I can't. Also the hard drive light doesn't blink so I think it just hangsup??? However in the ctr-alt-delete windo, it shows access as running....Any idea whats happening,

Thanks

>Any idea whats happening
I'm not sure.  I tested that code before posting it, and it worked fine...  If it "just keeps running", have you checked your table1 to see what if any updates were made?  Did you run the code through the Update Query I gave you, or did you run it from the VB editor?

I ran it through update query you gave me. I also checked the table and there were no records found in that table. I appreciate your quick response as I need it before meeting tomorrow.

Do you think it is because there could be more than one similar records in either of the 2 tables?

Thanks

Asim

can we do something like
msgbox 'if record is added

for each record that is added so I can check for few records and see if it is even working.

Thanks

ok my friend, it did work. I think there was some problem with my laptop. When I changed it to desktop, it worked. I do have one doubt about data integrity and I need an opinion from an expert like yourself. How accurate you think it matched the 2 files. There are 300,000+ records so I can check each and every one manually but I will check a random sample. But your hones opinion will count a lot.

Thanks

>300,000+ records

LOL!! I had a sneaking suspicion.  Yes... that will require plenty of time and patience.  Since you shut down the computer, run it again, walk away and just let it do it's thing.  I think the integrity will be very good .  The example you posted earlier would be a possible stumbling block where you are matching the first two words instead of just the number:

300 bridge creek Rd  Versus 300 bridgecreek rd

One has a space in the road name, the other doesn't.  There is also the potential for missed telephone numbers where one table has a typo in the road name and the other does not.  Other than that, the match is case insensitive and should work very well.  The Soundex URL that Nelson posted is worth checking into as well (I'm not very familiar with it myself), it may help out with possible typo issues.

Anyhow It's late here, and my eyes are slamming shut.  I will check back in tomorrow.

> Since you shut down the computer, run it again, walk away and just let it do it's thing
Rereading  your last post, It sounds like you already ran it again and let it complete its job?

One more quick note on the reliability.  The typos that I mentioned as potential issues will result in the phone number not being "picked up" in table1.  You won't get the phone number mismatched with the wrong person.  So  even if you run into a few typos those records should not be any worse off than they were before.

ok thank you so much.
You are truely a great person

Thanks

You're very welcome! Good luck with your project.

Thanks