Update records in 1 table based on match between 2 tables

R

rjphillips

I have 2 tables of around 30,000 records each. They both have a common
address field although the format of the address might not be the same in
each (I've included wildcards to try and get round this). I can manually
find addresses between each table using the Find tool in Access, so there are
matches. I need to either join these tables based on this address field
leaving all of the original records even if they are unmatched (ie. Inner
Join) or update fields in 1 table with data from the matching record in the
other table.

If have tried various SQL queries and VB code but to no avail. PLEASE HELP!!

My SQL is:

SELECT DTF.*, UNI.Address, UNI.UPRN AS Uni_UPRN, UNI.USRN AS Uni_USRN INTO
DTF_uni
FROM DTF LEFT JOIN UNI ON DTF.Address Like ('*' & UNI.Address & '*');

My VB is:

Sub AddressMatch()

Dim dbs As DAO.Database, dtf As DAO.Recordset, uniform As DAO.Recordset
Dim dtfAddress As String, USRN As Double, LPI As String, UPRN As String
Dim uniUPRN As DAO.Field, dtfUSRN As DAO.Field, address As DAO.Field
Dim uniUSRN As DAO.Field, field23 As DAO.Field, field24 As DAO.Field,
uniLPI As DAO.Field

Set dbs = CurrentDb
'Set dtf = dbs.OpenRecordset("DTF_24_LPI")
'Set uniform = dbs.OpenRecordset("Uniform_LPIs")
Set dtf = dbs.OpenRecordset("DTF_5000")
Set uniform = dbs.OpenRecordset("UnI_5000")

Set uniLPI = uniform("LPI")
Set address = dtf("Address")
Set uniUSRN = uniform("USRN")
Set uniUPRN = uniform("UPRN")
Set field23 = dtf("Field23")
Set field24 = dtf("Field24")
Set dtfUSRN = dtf("Uni_USRN")

uniform.MoveFirst
dtf.MoveFirst

Do While Not dtf.EOF
'Get address from dtf table and loop through uniform_lpi table to
match address.
'If match found then update fields in the dtf table with info from
the uniform table.
dtfAddress = address

Do While Not uniform.EOF
LPI = uniLPI
If Not uniLPI Like ("*" & dtfAddress & "*") Then
uniform.MoveNext
Else
dtf.Edit
USRN = uniUSRN
UPRN = uniUPRN
field23 = LPI
field24 = UPRN
dtfUSRN = USRN
dtf.Update
End If
Loop
uniform.MoveFirst
dtf.MoveNext
Loop

dbs.Close

End Sub

I'm using Access2002 BTW. Any help would be much appreciated.
 
Top