scan each row for a string from other table

gutmj

Registered User.
Local time
Today, 12:32
Joined
May 2, 2008
Messages
26
Hi All

I have address table with the following columns:

location_name
address1
address2
state_province
city
postal_code
country

the problem is that city details are all over the place (it can be within address1, address2, state).

I have another table that has cities of the world (two columns):
country
city

What I would like to achieve is to link two tables by country field and then scan each row of the address table against cities from the other table. If match was found the city in the address table would be moved to right column.

Can it be done in access? How would I approach it?

Thanks
Mark
 
you will need to do the following,

I have a table _Addresses which has Address 1, Address 2, Address 3 and City, suitably badly populated

I have _Cities, Whcih has Country, City

I then did the following, one for each address line in the table

Address 1

UPDATE _Addresses SET [_Addresses].City = [_Addresses]![Add1]
WHERE ((([_Addresses].Add1) In (SELECT DISTINCT [_Cities].City FROM _Cities;)));

Address 2

UPDATE _Addresses SET [_Addresses].City = [_Addresses]![Add2]
WHERE ((([_Addresses].Add1) In (SELECT DISTINCT [_Cities].City FROM _Cities;)));

Address 3

UPDATE _Addresses SET [_Addresses].City = [_Addresses]![Add3]
WHERE ((([_Addresses].Add1) In (SELECT DISTINCT [_Cities].City FROM _Cities;)));

and so on, this should do it. You can save each as a query and get a macro to run each in turn, or code them.

Thanks.
 
Thanks nathansav

My data is more messy. You can have street name, street number and city, all in one field "address1". What i'd like to do is to check if a part of the field value is a valid city name (compare against values from the city table). If it is remove it from "address1" and put into city field.
Don't know how to tackle this.

Cheers
Mark
 
This is possible, but i would do it in code. Is that something that you are prepared to look at?
 
I'd be happy to do it with code. Would it be easier to do in excel than in access? What do you think? Would you have any suggestions?

The only way I can think it can be done is to extract each element from the address field (the elements are 'space' separated) compare each element to the every element in the city table. If there's a match, populate the city field with the value that was matched and remove it from the address field.
It feels it will take a lot of processing time for a big table.
 
Hi,

Not sure which would be easiest to be honest. It has to go back into access, so may as well do it there.

Here is the way i'd do it in code, my tables are called _Cities and _Addresses

<code>

Option Compare Database
Private strNewAddress As String
Private strCityName As String
Sub Split_Address()
Dim strSQL As String
Dim rstCities As New ADODB.Recordset
Dim rstInspection As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim lngAddressID As Long
strSQL = "Select Distinct [City_Name] from [_Cities];"
Set con = Application.CurrentProject.Connection
rstCities.Open strSQL, con, 1
While Not rstCities.EOF
strCityName = rstCities.Fields("City_Name").Value

' Check column 1

strSQL = "SELECT Address_ID,[_Addresses].Address_1 FROM _Addresses;"
rstInspection.Open strSQL, con, 1

While Not rstInspection.EOF

If InStr(1, rstInspection.Fields("Address_1").Value, strCityName) <> 0 Then
lngAddressID = rstInspection.Fields("Address_ID").Value
strNewAddress = Remove_City(strCityName, rstInspection.Fields("Address_1").Value)
Update_Table "Address_1", lngAddressID
End If

rstInspection.MoveNext

Wend

rstInspection.Close

' Check column 2

strSQL = "SELECT Address_ID,[_Addresses].Address_2 FROM _Addresses;"
rstInspection.Open strSQL, con, 1

While Not rstInspection.EOF

If InStr(1, rstInspection.Fields("Address_2").Value, strCityName) <> 0 Then
lngAddressID = rstInspection.Fields("Address_ID").Value
strNewAddress = Remove_City(strCityName, rstInspection.Fields("Address_2").Value)
Update_Table "Address_2", lngAddressID
End If

rstInspection.MoveNext

Wend

rstInspection.Close

' Check column 3

strSQL = "SELECT Address_ID,[_Addresses].Address_3 FROM _Addresses;"
rstInspection.Open strSQL, con, 1

While Not rstInspection.EOF

If InStr(1, rstInspection.Fields("Address_3").Value, strCityName) <> 0 Then
lngAddressID = rstInspection.Fields("Address_ID").Value
strNewAddress = Remove_City(strCityName, rstInspection.Fields("Address_3").Value)
Update_Table "Address_3", lngAddressID
End If

rstInspection.MoveNext

Wend

rstInspection.Close

rstCities.MoveNext

Wend
rstCities.Close
Set rstCities = Nothing
Set con = Nothing
End Sub
Function Remove_City(strCityName As String, strAddress As String) As String
Dim intStartPosition As Integer
Dim intEndPosition As Integer
intStartPosition = InStr(1, strAddress, strCityName)
intEndPosition = intStartPosition + Len(strCityName)
Remove_City = Mid(strAddress, 1, intStartPosition - 1)
Remove_City = Remove_City & Mid(strAddress, intEndPosition, (Len(strAddress) - (intEndPosition - 1)))
End Function
Sub Update_Table(strFieldName As String, lngAddressID As Long)
Dim strSQL As String
strSQL = "UPDATE [_Addresses] SET [_Addresses]." & strFieldName & " = '" & _
strNewAddress & "', " & _
"[_Addresses].Address_City = '" & strCityName & "' " & _
"WHERE [_Addresses].Address_ID=" & _
lngAddressID & ";"

DoCmd.SetWarnings False: DoCmd.RunSQL strSQL: DoCmd.SetWarnings True
End Sub

</code>
 
Thanks for that

I'll look through the code and will try to apply it to my case.
Thanks again
 

Users who are viewing this thread

Back
Top Bottom