Matching lots of records

  • Thread starter Thread starter Stewart
  • Start date Start date
S

Stewart

I have 20000 records that I need to match up with 20000+
other records. The fields are a unique personal number,
date of birth, forename, surname, gender etc...

I can't just use the line matching method as I know that a
lot of the information may have altered, so they only
might match on 4 fields or 2 fields etc...

Is there any clever way of matching on all fields, then
removing those records as matched then 5 fields removing
the ones that match perfectly etc... 4 fields 3 fields
etc...

Stewart
 
Hi
Don't know about clever, or the myriad of possibilities of matching you
require.
I would possibly, depending on the details, do this in a VBA module. I take
it that
a) the records are in two separate tables.
b) the matching is on set fields

Have a column on tblNewRecs called AlreadyMatched default to false
Create qryMatch5 to select the match 5 records with an additional condition
AlreadyMatched = false

then create a sub in the modules window, makes sure tools-> references has
an ado library selected

' open connection and set up recordset code
' set up recordset to get new records.
set rs = ...
set rsNew = ...
rs.Open qryMatch5,dbConnection, .... '(readonly)
while not rs.EOF
'do stuff to be done with matched records
strSQL = "Select AlreadyMatched from tblNew WHERE Id = " & _
rs.Fields("Id")
rsNew.Open strSQL, .... (updateable)
rsNew.Fields("AlreadyMatched") = true
rsNew.Update
rsNew.Close
rs.MoveNext
wend

' close and set recordsets to nothing, etc
rs,Close
set rs = nothing
set rsNew = nothing

repeat for as many steps as necessary, such as qryMatch4, qryMatch3.

hth
Marc
 
Back
Top