Recordset and querydef for matching up to four varying columns

[starley_rover]

Dear all

I am trying to match records in two tables (tblNicksExtract, xlatest) using four fields. In both tables there are alternative soundexes, dates of birth, patient numbers, and I may also substitute further matching criteria, such as postcodes. In all there may be a hundred or more permutations of the different matching criteria, and these field combinations are listed in tblMatchtype. In the long term, I feel that writing individual queries for every permutation would be impractical, and almost guarantee errors. I would prefer to attack this via a querydef/recordset, but have so far not succeeded in recording the querydef (my strSQLTarget below is shown as empty). I am not sure if this is the most appropriate approach to this problem, and confess I am a little stuck.

Public Sub vbaAppendSGSSMatch()

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim qdfTemp As DAO.QueryDef
    Dim strSQL, strSQLTarget as String
    Set dbs = CurrentDb
    ' Remove existing query produced by earlier run of querydef (if found)
    For Each qdfTemp In CurrentDb.QueryDefs
        If qdfTemp.Name = "qappSGSSMatch" Then
            CurrentDb.QueryDefs.Delete "qappSGSSMatch"
            Exit For
        End If
    Next
'   matchtype key field
    strSQL = "SELECT matchtype, Nick1, x1, Nick2, x2, Nick3, x4 FROM tblMatchType"
    ' Get the recordset to point to query
    Set rst = dbs.OpenRecordset(strSQL)
    
    rst.MoveFirst
    'DoCmd.SetWarnings (WarningsOff)
    
    Do While Not rst.EOF
        Debug.Print rst!matchtype
        strSQLTarget = "INSERT INTO tblMatch ( SGSS_ID, ID, clinicid, sdex, firstdate, earliesteventdate, genderid, matchtype )" & _
        "SELECT tblNicksExtract.SGSS_ID, xlatest.ID, xlatest.clinicid, tblNicksExtract.sdex, tblNicksExtract.firstdate, " & _
        " xlatest.earliesteventdate, xlatest.genderid, rst.matchtype AS matchtype " & _
        " FROM tblNicksExtract INNER JOIN xlatest " & _
        " ON  (tblNicksExtract!'" & rst!Nick1 & "' = xlatest!'" & rst!x1 & "' )" & _
        " AND (tblNicksExtract!'" & rst!Nick2 & "' = xlatest!'" & rst!x2 & "' )" & _
        " AND (tblNicksExtract!'" & rst!Nick3 & "' = xlatest!'" & rst!x3 & "' )" & _
        " AND (tblNicksExtract!'" & rst!Nick4 & "' = xlatest'!" & rst!x4 & "');"
        Debug.Print strSQLTarget
        'DoCmd.RunSQL strSQLtarget  needs to be an action query to run, add "into tblMatch " (before FROM...)
        
        Set qdfTemp = dbs.CreateQueryDef("qappSGSSMatch", strSQLTarget)
        DoCmd.RunSQL strSQLTarget
      
        rst.MoveNext
        dbs.QueryDefs.Delete "qappSGSSMatch"
    Loop
    
'   Cleanup
    'DoCmd.SetWarnings (WarningsOn)
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing

End Sub

Thanks for reading
Mark

 

[Deleted member 147267]

(my strSQLTarget below is shown as empty)
You mean this Debug.Print strSQLTarget produces no output in the immediate window?

And why run the sql if you have just set qdef qappSGSSMatch to contain that sql? Why not execute the qdef? Also, note that strSQL is a variant - not that it will matter here - because you failed to explicitly type it
Dim strSQL As String, strSQLTarget as String
Lastly IMHO, continually creating/deleting a stored query causes some bloat and according to some, risks corruption. If you're going to have all this code, you can alter the sql property of a qdef on the fly (without continually deleting the qdef) or you can create a qdef that resides only in memory by not giving it a name. I hate to call that one a temporary qdef as it lends itself to the notion of continually deleting and recreating it. To me, a temporary qdef is one that only exists in machine memory.

 

[MajP]

https://www.access-programmers.co.uk/forums/showthread.php?t=303677&page=5

You may want to look at this thread or at least the last posted DB. This was designed to allow you to match any number of fields to include matching nulls. The last DB I have a form where you can pick two tables. You match the fields you want to match (kind of like a wizard), and save the mapping. It then builds the sql to use in determining matches. It is a long thread, but sounds somewhat similar and maybe could be adapted.