Access VBA looping through collection, Making SQL statement for each item

Question

I am trying to write code that makes a collection of associate IDs (Associates is the name of my collection). There are 10 associates at any given time, but the collection will change based on who did what work this month. So once the collection has been made, I want to loop through it and make an SQL statement for each item. Some thing kind of like this:

        For Each Item In Associates
            qryTopSQL = "SELECT TOP " & QA# & _
            " Date, ID, [L#], Deal, RndNum FROM tbl_Data WHERE Date Between #" & _
            StartDate & "# And #" & EndDate & "# AND ID = " & Associates() & _
            " ORDER BY RndNum"
        Next Item

So I end up with however many SQL strings, but I'm having problems with this:

1. Am I writing the ID = " & Associates() & " part correctly?
2. How will it name these different strings so that I may access them later?
3. Once it makes these, I'd like to do a UNION ALL query for all the SQL strings. How would I do this?

Please help if you can, it's greatly appreciated. I'm new to collections and arrays and I don't understand some of the things I've found online.

EDIT for an update: I tried this:

j = 1
k = 1
For Each Item In Associates
    If j = 1 And k = 1 Then
        qryTopString1 = "SELECT * FROM qryTopSQL_" & k
    Else
        qryTopString2 = " UNION ALL SELECT * FROM qryTopSQL_" & k
    End If
j = j + 1
k = k + 1
Next Item

'
Set qryTopUnionqdef = CurrentDb.CreateQueryDef("qryTopSQLUnion", qryTopString1 & qryTopString2)

But the resulting query is a union between the first and last TopSQLs, and none in the middle. Clearly the loop at this point it the problem but I can't figure out what to do thus far.

Answer 1

In Access there are two ways to create query objects: VBA queries (in code) or stored queries (using ribbon, wizard, or navigation bar).

Essentially, you want to do both. So in order to migrate your VBA SQL strings into actual stored query objects, you must use QueryDefs. Below is how to iterate to dynamically create the 10 Associates queries and one union query.

Dim qryTopqdef As QueryDef, qryTopUnionqdef As QueryDef, findqdf As QueryDef
Dim i as Integer, j as Integer

' DELETE QUERIES IF EXIST
For each findqdf in CurrentDb.Querydefs
    If Instr(findqdf.Name, "qryTopSQL") > 0 Then
        db.QueryDefs.delete(findqdf.Name)
    End if
Next findqdf

' INDIVIDUAL 10 QUERIES
i = 1
For Each Item In Associates

    qryTopSQL = "SELECT TOP " & QA# & _
            " Date, ID, [L#], Deal, RndNum FROM tbl_Data WHERE Date Between #" & _
            StartDate & "# And #" & EndDate & "# AND ID = " & Item & _
            " ORDER BY RndNum"

    ' QUERY NAMES ARE SUFFIXED BY THE ITERATOR COUNT
    Set qryTopqdef = CurrentDb.CreateQueryDef("qryTopSQL_" & i, qryTopSQL)
    i = i + 1
Next Item


' UNION QUERY
j = 1
For Each Item In Associates
    If j = 1 Then
        qryTopSQL = "SELECT TOP " & QA# & _
                " Date, ID, [L#], Deal, RndNum FROM tbl_Data WHERE Date Between #" & _
                StartDate & "# And #" & EndDate & "# AND ID = " & Item & _
                " ORDER BY RndNum"
    Else
        ' UNIONS ARE SIMPLY STACKS OF SELECT STATEMENTS OF SAME COLUMN NUMBER AND DATA TYPE
        ' TOGETHER JOINED BY THE UNION OR UNION ALL CLAUSE
        qryTopSQL = qryTopSQL & " UNION SELECT TOP " & QA# & _
                " Date, ID, [L#], Deal, RndNum FROM tbl_Data WHERE Date Between #" & _
                StartDate & "# And #" & EndDate & "# AND ID = " & Item & _
                " ORDER BY RndNum"
    End if
    j = j + 1
Next Item

Set qryTopUnionqdef = CurrentDb.CreateQueryDef("qryTopSQLUnion", qryTopSQL)


' UNINTIALIZE OBJECTS
Set qryTopqdef = nothing
Set qryTopUnionqdef = nothing

Also - see this SO post on collections vs arrays