MS-Access VBA SQL, If condition in subquery, efficiency and improvement

Question

I've managed to get my code to work, but it takes a very long time to execute. I use a series of open and close recordset statements to get the addr_ImportID I'm looking for before executing a final SQL statement and writing the row to a table.

Is there a better way to do what I'm trying to achieve using a better subquery, case statements, if condition or otherwise? My usage of subqueries is slowing things down, so maybe manipulating the data without opening and closing recordsets would be more efficient. Or a better series of select statements?

Here's a sample of what the table looks like:

CnBio_ID | Addr_Type | Addr_ImportID | CnAdrAll_1_IDKEY | CnAdrAll_1_DateLastChanged
0001009  | Previous  | 12344         | 1                | 03/15/2014
0001009  | Home      | H2345         | 3                | 01/02/2017
0001009  | Home      | A2344         | 2                | 01/02/2017
1223144  | Previous  | 23441         | 7                | 05/23/2010
1223144  | Other     | 31333         | 6                | 08/21/2016
1223144  | Previous  | 45848         | 5                | 08/21/2016

The snippet below is one section of my code.

strSql2 = "SELECT Addr_ImportID From [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' " & _
        "AND CnAdrAll_1_DateLastChanged IN (SELECT Max(CnAdrAll_1_DateLastChanged) FROM [Non-Preferred Home Addresses] " & _
        "Where CnBio_ID = '" & rs(0) & "' AND Addr_ImportID IN (SELECT Addr_ImportID From [Non-Preferred Home Addresses] " & _
        "WHERE CnBio_ID = '" & rs(0) & "' AND Addr_Type = 'Home'));"
        Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
        If Not (rsMaxAddressImportID.EOF) Then
            'Condition where more than one Home address exists updated on the same date
            If (rsMaxAddressImportID.RecordCount > 1) Then
                rsMaxAddressImportID.Close
                strSql2 = "SELECT Addr_ImportID FROM [Non-Preferred Home Addresses] WHERE CnAdrAll_1_IDKEY = " & _
                "(SELECT Max(CnAdrAll_1_IDKEY) FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' AND Addr_Type = 'Home');"
                Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
                MsgBox "Row = " & i
            End If

For Each CnBio_ID
If Home address exists, select Addr_ImportID of row with Max DateLastChanged
- If multiple Home addresses last updated on the same date, select Addr_ImportID of row with Max CnAdrAll_1_IDKEY
If no Home address, select Addr_ImportID of row with Max DateLastChanged
- If multiple addresses last updated on the same date, select Addr_ImportID of row with max CnAdrAll_1_IDKEY

Updated after question by Parfait

'Select Distinct CIDs to loop through
'strSql = "SELECT CnBio_ID FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & ConstID & "'Group By CnBio_ID;"
strSql = "SELECT CnBio_ID FROM [Non-Preferred Home Addresses] Group By CnBio_ID;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

If Not (rs.EOF And rs.BOF) Then
    'MsgBox rs.RecordCount

    'This section will loop through all distinct CIDs.
    i = 1
    Do Until rs.EOF

        'Select Non-Preferred Home Address with latest date updated
        strSql2 = "SELECT Addr_ImportID From [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' " & _
        "AND CnAdrAll_1_DateLastChanged IN (SELECT Max(CnAdrAll_1_DateLastChanged) FROM [Non-Preferred Home Addresses] " & _
        "Where CnBio_ID = '" & rs(0) & "' AND Addr_ImportID IN (SELECT Addr_ImportID From [Non-Preferred Home Addresses] " & _
        "WHERE CnBio_ID = '" & rs(0) & "' AND Addr_Type = 'Home'));"
        Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
        If Not (rsMaxAddressImportID.EOF) Then
            'Condition where more than one Home address exists updated on the same date
            If (rsMaxAddressImportID.RecordCount > 1) Then
                rsMaxAddressImportID.Close
                strSql2 = "SELECT Addr_ImportID FROM [Non-Preferred Home Addresses] WHERE CnAdrAll_1_IDKEY = " & _
                "(SELECT Max(CnAdrAll_1_IDKEY) FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' AND Addr_Type = 'Home');"
                Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
                MsgBox "Row = " & i
            End If

            'rsMaxAddressImportID.Close
        Else
            'Condition if all non-preferred addresses are not Home
            rsMaxAddressImportID.Close

            'Select max date last updated for next select statement
            maxDateSql = "SELECT Max(CnAdrAll_1_DateLastChanged) " & _
            "FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "';"
            Set rs2 = DBEngine(0)(0).OpenRecordset(maxDateSql, dbOpenDynaset)

            strSql2 = "SELECT Addr_ImportID FROM [Non-Preferred Home Addresses] " & _
            "WHERE [Non-Preferred Home Addresses].CnBio_ID = '" & rs(0) & "' " & _
            "AND [Non-Preferred Home Addresses].CnAdrAll_1_DateLastChanged = #" & rs2(0) & "#;"
            Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
            'MsgBox rsMaxAddressImportID(0)
            If Not (rsMaxAddressImportID.EOF) Then
                'MsgBox rsMaxAddressImportID.RecordCount
                If (rsMaxAddressImportID.RecordCount > 1) Then
                    rsMaxAddressImportID.Close
                    strSql2 = "SELECT Addr_ImportID FROM [Non-Preferred Home Addresses] WHERE CnAdrAll_1_IDKEY = " & _
                    "(SELECT Max(CnAdrAll_1_IDKEY) FROM [Non-Preferred Home Addresses] WHERE CnBio_ID = '" & rs(0) & "' AND CnAdrAll_1_DateLastChanged = #" & rs2(0) & "#);"
                    Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
                    'MsgBox rsMaxAddressImportID(0)
                End If

            End If

        End If

        'Get Actual address line
        'DoCmd.RunSQL "SELECT * INTO " & _
        "[Tbl_Max_Non-Preferred_Address] " & _
        "FROM [Non-Preferred Home Addresses] WHERE [Non-Preferred Home Addresses].Addr_ImportID = '" & rsMaxAddressImportID(0) & "';"

        DoCmd.RunSQL "INSERT INTO [Tbl_Max_Non-Preferred_Address] SELECT * FROM [Non-Preferred Home Addresses] WHERE [Non-Preferred Home Addresses].Addr_ImportID = '" & rsMaxAddressImportID(0) & "';"

        rsMaxAddressImportID.Close
        rs.MoveNext
        i = i + 1

    Loop
Else
    MsgBox "There are no records in the recordset."
End If

rs.Close

The significance of "Home" address is, some Constituents do not have a "Home" address (see CnBio_ID 1223144).
rs is a Selection of unique CnBio_IDs to loop through.

Update after solution by Thomas G:

Overall, everything seems to get a good result, but I think there is sorting/order by issue going on and I can't seem to get the syntax correct to test. I keep getting a syntax error. I'm not familiar enough with access interpretation of parenthesis, etc. to get it right. I'm trying to run the following:

strSql_1 = "INSERT INTO [Table1] SELECT 1 AS priority, T1.CnBio_ID, T2.Max_Date, MAX(T1.Addr_IDKEY) AS MAX_IDKey " & _
        "FROM [TB - Non-Preferred Addresses] T1 " & _
        "INNER JOIN " & _
        "(" & _
            "SELECT CnBio_ID , MAX(CnAdrAll_1_DateLastChanged) AS Max_Date " & _
            "FROM [TB - Non-Preferred Addresses] " & _
            "WHERE (Addr_Type = 'Home' AND CnBio_ID = '0106228') " & _
            "GROUP BY CnBio_ID " & _
        ") T2 ON T1.CnBio_ID = T2.CnBio_ID AND T1.CnAdrAll_1_DateLastChanged = T2.Max_Date " & _
        "WHERE Addr_Type = 'Home' " & _
        "GROUP BY T1.CnBio_ID, T2.Max_Date "
strSql_2 = " UNION ALL SELECT 2 AS priority, T1.CnBio_ID, T2.Max_Date, MAX(T1.Addr_IDKEY) AS MAX_IDKey " & _
        "FROM [TB - Non-Preferred Addresses] T1 " & _
        "INNER JOIN " & _
        "(" & _
            "SELECT CnBio_ID , MAX(CnAdrAll_1_DateLastChanged) AS Max_Date " & _
            "FROM [TB - Non-Preferred Addresses] " & _
            "WHERE (Addr_Type IN ('Campus','Harris Home', 'NCOA2003','Other','Previous','Previous Home','Previous Student','Seasonal','Student') AND CnBio_ID = '0106228') " & _
            "GROUP BY CnBio_ID " & _
        ") T2 ON T1.CnBio_ID = T2.CnBio_ID AND T1.CnAdrAll_1_DateLastChanged = T2.Max_Date " & _
        "WHERE (Addr_Type IN ('Campus','Harris Home', 'NCOA2003','Other','Previous','Previous Home','Previous Student','Seasonal','Student') AND T1.CnBio_ID = '0106228') " & _
        "GROUP BY T1.CnBio_ID, T2.Max_Date " & _
        "ORDER BY CnBio_ID, priority"

What is the significance of *Home* address as criteria seems to be same with or without. And please provide a larger code block as *rs* is not known. Desired result would help illustrate as well. — Parfait, Apr 22 '18 at 03:01
Thank you for looking at my question. I've updated my post above with full code snippet and explanation of "Home" and rs. — Jeffrey W, Apr 22 '18 at 03:37
Nice question. To complete Parfait : add a debug.print of all your SQL instructions and post them. atm Its hard to read and to figure out what all your rs(x) fields becomes — Thomas G, Apr 22 '18 at 07:50
Yes. Addr_ImportID is unique. I’ll look up using debug.print when I’m back at work on Monday. Thank you for your responses. — Jeffrey W, Apr 22 '18 at 17:30

Thomas G · Accepted Answer · 2018-04-28T07:54:08.117

This should do the trick in pure SQL

SELECT V.CnBio_ID, FIRST(T.Addr_ImportID) AS Addr_ImportID , FIRST(V.Max_Date) AS CnAdrAll_1_DateLastChanged, FIRST(V.MAX_IDKey) AS CnAdrAll_1_IDKEY 
FROM [Non-Preferred Home Addresses] T
INNER JOIN
(

    SELECT 1 AS priority,  T1.CnBio_ID, T2.Max_Date, MAX(T1.CnAdrAll_1_IDKEY) AS MAX_IDKey
    FROM [Non-Preferred Home Addresses] T1
    INNER JOIN
    (
        SELECT CnBio_ID , MAX(CnAdrAll_1_DateLastChanged) AS Max_Date
        FROM [Non-Preferred Home Addresses]
        WHERE Addr_Type = 'Home'
        GROUP BY CnBio_ID
    ) T2 ON T1.CnBio_ID=T2.CnBio_ID AND T1.CnAdrAll_1_DateLastChanged=T2.Max_Date
    WHERE Addr_Type = 'Home'
    GROUP BY T1.CnBio_ID, T2.Max_Date

    UNION ALL

    SELECT 2 AS priority, T1.CnBio_ID, T2.Max_Date, MAX(T1.CnAdrAll_1_IDKEY) AS MAX_IDKey
    FROM [Non-Preferred Home Addresses] T1
    INNER JOIN
    (
        SELECT CnBio_ID , MAX(CnAdrAll_1_DateLastChanged) AS Max_Date
        FROM [Non-Preferred Home Addresses]
        WHERE Addr_Type <> 'Home'
        GROUP BY CnBio_ID
    ) T2 ON T1.CnBio_ID=T2.CnBio_ID AND T1.CnAdrAll_1_DateLastChanged=T2.Max_Date
    WHERE Addr_Type <> 'Home'
    GROUP BY T1.CnBio_ID, T2.Max_Date

    ORDER BY CnBio_ID, priority

) V 
ON T.CnBio_ID = V.CnBio_ID AND T.CnAdrAll_1_DateLastChanged=V.Max_Date AND T.CnAdrAll_1_IDKEY = V.MAX_IDKey
GROUP BY V.CnBio_ID

For each CnBio_ID in your table, it will keep the record matching your dates logic. You can make a VIEW out of it (a query), and then query it with CnBio_ID=anything to retrieve the info for any CnBio_ID

However I don't know if the performance will be acceptable, mostly depending on the number of records in your table.

You should have indexes on :

CnBio_ID
Addr_Type
CnAdrAll_1_IDKEY
CnAdrAll_1_DateLastChanged

If you have a limited number of types being not "Home", it is much better, from a perf point o view, to name them all explicitly instead of searching "<> 'HOME'", so you have to change this :

WHERE Addr_Type <> 'Home'

to this

WHERE Addr_Type IN ('Previous','Other')

(2 times)

Let me know if you really don't understand what happens, and if you want some explanations. I don't want to engage in long explanations for nothing. Start by decomposing and run all the sub queries and you should understand it better.

UPDATE

To use my query in your code, don't hardcore it in VBA! Use the query designer to make a Query out of it (in standard SQL this is called a VIEW). Query Designer Example in MS Access 2016

When you're in the designer, switch to SQL View. COpy/Paste my query and Save it. Name it for Instance QRY_foo

It will now act as a table

Then in your VBA, just do this :

strSql2 = "SELECT * FROM QRY_Foo"
Set rsMaxAddressImportID = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

Then you will have easier to tweak the query, just do it from the Query designer.

I will give this a try when I’m back at work tomorrow. I’ll do my best to figure it all out before asking follow up questions, as understanding what is going on is going to help me do more of this on future project. Thank you again. — Jeffrey W, Apr 22 '18 at 17:32
Thank you Thomas G. I've done a lot of work to figure this out, including creating a table to hold the data (previously, I was selecting from a query) and created indexes. I've translated your SQL and and created some temp tables hold the results from the subqueries, just to see what is going on. And I'm running into this error: "Error 3417 An action query cannot be used as a row source." I'd paste the select statement I'm using, but it contains too many characters. — Jeffrey W, Apr 23 '18 at 16:02
Disregard my previous comment about the error. I edited the SQL statement to account for updated table names and the query works. VERY quickly, I might add. I'll do some data validation to verify accuracy. My next lesson is to figure out how you've used the MAX() function and GROUP BY conditions to get one correct value per CnBio_ID. Thank you for your help. — Jeffrey W, Apr 23 '18 at 16:21
Overall, everything seems to get a good result, but I think there is sorting/order by issue going on and I can't seem to get the syntax correct. I'm trying to run the following: — Jeffrey W, Apr 27 '18 at 15:18
Can someone take a look at my updated question today regarding syntax to test the sorting/order by in the Union All statement? — Jeffrey W, Apr 27 '18 at 15:24
I didn't think to use the Design Tool. Once I did so, I found that the final join was causing a sorting issue. T sorting was taking precedence. So change the join and it seems to work. See the following snippet. `SELECT V.CnBio_ID AS CnBio_ID, First(T.Addr_ImportID) AS FirstOfAddr_ImportID, First(V.Max_Date) AS CnAdrAll_1_DateLastChanged, First(V.MAX_IDKey) AS Addr_IDKEY INTO [Tbl_Max_Non-Preferred_Address] FROM ( ... ) AS V LEFT JOIN [TB - Non-Preferred Addresses] AS T ON V.MAX_IDKey = T.Addr_IDKEY GROUP BY V.CnBio_ID;` — Jeffrey W, Apr 30 '18 at 21:26

MS-Access VBA SQL, If condition in subquery, efficiency and improvement

1 Answers1