Updating data according to multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I'm new to Access, and I'm looking for the most efficient way of structuring
an update query.
I get a new data file every month, and each time I have to clean it up.
Let's say, it contains addresses, and I have to change all references of
"Streets" to "St.", "Roads" to "Rd." and "Boulevards" to "Blvd.", but there
are 50+ such changes that have to be made. Should I create an update query
for each of the 50 changes that are required, and somehow combine all of them
in a macro that can be run every month, or is there a better way?
Thanks, Graeme
 
One way to do it is to build a table with the oldvalue and the newvalue.
Then use that table's records to update the new data.

The code below uses that table to make the replacements. No guarantees on
the code, since I pulled it out of an old database and cleaned it up a bit.
It should at least give you ONE idea of how to proceed.


Public Function fNormalizeAddresses()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim strOld As String, strNew As String, strMatch As String

Set dbAny = CurrentDb()

'===============================================================
' Normalize the street address (Replace Street with St, etc)
'===============================================================

strSQL = "SELECT OldValue, NewValue " & _
" FROM tblAddressNormalization " & _
" ORDER By SortOrder"
Set rstAny = dbAny.OpenRecordset(strSQL)

With rstAny

While .EOF = False

strOld = !OldValue
strNew = !NewValue
strMatch = Replace(strOld, "#", "[#]")

strOld = " " & strOld & " "
strNew = " " & strNew & " "

strSQL = "UPDATE tblSampleImport" & _
" SET Address1_Normalized = TRIM(" & _
" Replace("" "" & Address1_Normalized & "" "",""" & strOld & _
""", """ & strNew & """))" & _
" WHERE Address1_Normalized Like ""*" & strMatch & "*"""

dbAny.Execute strSQL, dbFailOnError

.MoveNext
Wend

End With

End Function




John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Graeme, just be aware that no matter what approach you take (and John's solution is a good first run), you'll still have to go through and correct some manually. For example, this will change St. Mary's Avenue to Street Mary's Avenue.

I don't think you'll find a solution that will work 100% without proofing.

--
Joan Wild
Microsoft Access MVP
John Spencer said:
One way to do it is to build a table with the oldvalue and the newvalue.
Then use that table's records to update the new data.

The code below uses that table to make the replacements. No guarantees on
the code, since I pulled it out of an old database and cleaned it up a bit.
It should at least give you ONE idea of how to proceed.


Public Function fNormalizeAddresses()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim strOld As String, strNew As String, strMatch As String

Set dbAny = CurrentDb()

'===============================================================
' Normalize the street address (Replace Street with St, etc)
'===============================================================

strSQL = "SELECT OldValue, NewValue " & _
" FROM tblAddressNormalization " & _
" ORDER By SortOrder"
Set rstAny = dbAny.OpenRecordset(strSQL)

With rstAny

While .EOF = False

strOld = !OldValue
strNew = !NewValue
strMatch = Replace(strOld, "#", "[#]")

strOld = " " & strOld & " "
strNew = " " & strNew & " "

strSQL = "UPDATE tblSampleImport" & _
" SET Address1_Normalized = TRIM(" & _
" Replace("" "" & Address1_Normalized & "" "",""" & strOld & _
""", """ & strNew & """))" & _
" WHERE Address1_Normalized Like ""*" & strMatch & "*"""

dbAny.Execute strSQL, dbFailOnError

.MoveNext
Wend

End With

End Function




John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Graeme said:
Hi,
I'm new to Access, and I'm looking for the most efficient way of
structuring
an update query.
I get a new data file every month, and each time I have to clean it up.
Let's say, it contains addresses, and I have to change all references of
"Streets" to "St.", "Roads" to "Rd." and "Boulevards" to "Blvd.", but
there
are 50+ such changes that have to be made. Should I create an update
query
for each of the 50 changes that are required, and somehow combine all of
them
in a macro that can be run every month, or is there a better way?
Thanks, Graeme
Click to expand...
Click to expand...
 
Joan,
Your advice is good, but your example is bad.

He would be changing "Street" to "St"

However if he were changing North to N then he could run into problems with

421 West North Street

as that could become
421 W N St

There are more complex methods of handling the situation, but as you said it
would probably be a wise idea to apply someone's brain. I actually use two
fields in the table(Address) and (Address_Normalized) so I can keep the
original input for review/comparison. Also with a lot of changes I am
careful about the order the changes are applied so that I don't change a
value that I've already changed.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Graeme, just be aware that no matter what approach you take (and John's
solution is a good first run), you'll still have to go through and correct
some manually. For example, this will change St. Mary's Avenue to Street
Mary's Avenue.

I don't think you'll find a solution that will work 100% without proofing.

--
Joan Wild
Microsoft Access MVP
John Spencer said:
One way to do it is to build a table with the oldvalue and the newvalue.
Then use that table's records to update the new data.

The code below uses that table to make the replacements. No guarantees on
the code, since I pulled it out of an old database and cleaned it up a
bit.
It should at least give you ONE idea of how to proceed.


Public Function fNormalizeAddresses()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim strOld As String, strNew As String, strMatch As String

Set dbAny = CurrentDb()

'===============================================================
' Normalize the street address (Replace Street with St, etc)
'===============================================================

strSQL = "SELECT OldValue, NewValue " & _
" FROM tblAddressNormalization " & _
" ORDER By SortOrder"
Set rstAny = dbAny.OpenRecordset(strSQL)

With rstAny

While .EOF = False

strOld = !OldValue
strNew = !NewValue
strMatch = Replace(strOld, "#", "[#]")

strOld = " " & strOld & " "
strNew = " " & strNew & " "

strSQL = "UPDATE tblSampleImport" & _
" SET Address1_Normalized = TRIM(" & _
" Replace("" "" & Address1_Normalized & "" "",""" & strOld & _
""", """ & strNew & """))" & _
" WHERE Address1_Normalized Like ""*" & strMatch & "*"""

dbAny.Execute strSQL, dbFailOnError

.MoveNext
Wend

End With

End Function




John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Graeme said:
Hi,
I'm new to Access, and I'm looking for the most efficient way of
structuring
an update query.
I get a new data file every month, and each time I have to clean it up.
Let's say, it contains addresses, and I have to change all references of
"Streets" to "St.", "Roads" to "Rd." and "Boulevards" to "Blvd.", but
there
are 50+ such changes that have to be made. Should I create an update
query
for each of the 50 changes that are required, and somehow combine all of
them
in a macro that can be run every month, or is there a better way?
Thanks, Graeme
Click to expand...
Click to expand...
 
John Spencer said:
Joan,
Your advice is good, but your example is bad.
Click to expand...

You're right, I didn't read his original post carefully. But my point was that he shouldn't just blindly run with the code.
 
Back
Top