Looped SQL for Complex VBA Query

I'm trying to move data from multiple tables into a single table.  The table names are similar and so I thought I would create a "periods" table (named 'tblTable_Names') and use that to loop 'MEMBERLIST ' & 201211, 201212, 201301, . . . to 201312.  The query is below but everywhere one sees the word 'MEMBERLIST' it's really 'MEMBERLIST 201211' or 'MEMBERLIST 201212.'

So I started just with the " FROM MEMBERLIST " & [" & rstTables.Fields(0) & "]" _ statement.  It turned red so I knew I'd not get success in the earlier SQL parts.  Please help me to loop this.

Dim rstTables As DAO.Recordset
Dim rstGroup As DAO.Recordset
Dim strSql As String
    Set Db = CurrentDb
    Set rstTables = _
      Db.OpenRecordset("tblTable_Names", dbOpenTable)

rstTables.MoveFirst
Do While Not rstTables.EOF
        strSql = "INSERT INTO [tblASO_Groups_CS MEMBERLIST] ( BILLING_PERIOD, Entity, Group_No, EMPLOYER, EMPLOYER_GRP_ID, PRODUCT_CODE, RISK, SumOfFEE_AMOUNT )" & _
        " SELECT MEMBERLIST.BILLING_PERIOD, tbl_Lookup_Entity.Entity, IIf(Len([MEMBERLIST]![PRODUCT_CODE])<10,[MEMBERLIST]![EMPLOYER_GRP_ID],IIf(InStr(8,[MEMBERLIST]![PRODUCT_CODE]," - ")>0,Mid([MEMBERLIST]![PRODUCT_CODE],InStr(5,[MEMBERLIST]![PRODUCT_CODE]," - ")+1,InStr(8,[MEMBERLIST]![PRODUCT_CODE]," - ")-6),Right([MEMBERLIST]![PRODUCT_CODE],9))) AS Group_No, MEMBERLIST.EMPLOYER, MEMBERLIST.EMPLOYER_GRP_ID, MEMBERLIST.PRODUCT_CODE, MEMBERLIST.RISK, Sum(MEMBERLIST.FEE_AMOUNT) AS SumOfFEE_AMOUNT" & _
        " FROM MEMBERLIST " & [" & rstTables.Fields(0) & "]" _
        "LEFT JOIN tbl_Lookup_Entity ON MEMBERLIST.LEGAL_ENTITY = tbl_Lookup_Entity.LEGAL_ENTITY
        "GROUP BY MEMBERLIST.BILLING_PERIOD, tbl_Lookup_Entity.Entity, IIf(Len([MEMBERLIST]![PRODUCT_CODE])<10,[MEMBERLIST]![EMPLOYER_GRP_ID],IIf(InStr(8,[MEMBERLIST]![PRODUCT_CODE],"-")>0,Mid([MEMBERLIST]![PRODUCT_CODE],InStr(5,[MEMBERLIST]![PRODUCT_CODE],"-")+1,InStr(8,[MEMBERLIST]![PRODUCT_CODE],"-")-6),Right([MEMBERLIST]![PRODUCT_CODE],9))), MEMBERLIST.EMPLOYER, MEMBERLIST.EMPLOYER_GRP_ID, MEMBERLIST.PRODUCT_CODE, MEMBERLIST.RISK;"
       
Debug.Print strSql
DoCmd.RunSQL strSql
rstTables.MoveNext
Loop
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this


Sub test()
Dim rstTables As DAO.Recordset, db As DAO.Database
Dim rstGroup As DAO.Recordset
Dim strSql As String
    Set db = CurrentDb
    Set rstTables = _
      db.OpenRecordset("tblTable_Names", dbOpenTable)
Dim strTable As String
rstTables.MoveFirst
Do While Not rstTables.EOF
        strTable = "[MEMBERLIST " & rstTables(0) & "]"
       
  strSql = "INSERT INTO [tblASO_Groups_CS MEMBERLIST] ( BILLING_PERIOD, Entity, Group_No, EMPLOYER, EMPLOYER_GRP_ID, PRODUCT_CODE, RISK, SumOfFEE_AMOUNT )" _
     & " SELECT M.BILLING_PERIOD, tbl_Lookup_Entity.Entity, IIf(Len([M]![PRODUCT_CODE])<10,[M]![EMPLOYER_GRP_ID],IIf(InStr(8,[M]![PRODUCT_CODE],'-')>0,Mid([M]![PRODUCT_CODE],InStr(5,[M]![PRODUCT_CODE],'-')+1,InStr(8,[M]![PRODUCT_CODE],'-')-6),Right([M]![PRODUCT_CODE],9))) AS Group_No, M.EMPLOYER, M.EMPLOYER_GRP_ID, M.PRODUCT_CODE, M.RISK, Sum(M.FEE_AMOUNT) AS SumOfFEE_AMOUNT" _
     & " FROM " & strTable & " AS M" _
     & " LEFT JOIN tbl_Lookup_Entity ON M.LEGAL_ENTITY = tbl_Lookup_Entity.LEGAL_ENTITY" _
     & " GROUP BY M.BILLING_PERIOD, tbl_Lookup_Entity.Entity, IIf(Len([M]![PRODUCT_CODE])<10,[M]![EMPLOYER_GRP_ID],IIf(InStr(8,[M]![PRODUCT_CODE],'-')>0,Mid([M]![PRODUCT_CODE],InStr(5,[M]![PRODUCT_CODE],'-')+1,InStr(8,[M]![PRODUCT_CODE],'-')-6),Right([M]![PRODUCT_CODE],9))), M.EMPLOYER, M.EMPLOYER_GRP_ID, M.PRODUCT_CODE, M.RISK;"


DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
rstTables.MoveNext
Loop

       
End Sub
0
 
Rey Obrero (Capricorn1)Commented:
try this


SELECT M.BILLING_PERIOD, tbl_Lookup_Entity.Entity, IIf(Len(M![PRODUCT_CODE])<10,M![EMPLOYER_GRP_ID],IIf(InStr(8,M![PRODUCT_CODE]," - ")>0,Mid(M![PRODUCT_CODE],InStr(5,M![PRODUCT_CODE]," - ")+1,InStr(8,M![PRODUCT_CODE]," - ")-6),Right(M![PRODUCT_CODE],9))) AS Group_No, M.EMPLOYER, M.EMPLOYER_GRP_ID, M.PRODUCT_CODE, M.RISK, Sum(M.FEE_AMOUNT) AS SumOfFEE_AMOUNT" & _
        " FROM " & "[" & MEMBERLIST " & rstTables.Fields(0) & "]"  As M _
        "LEFT JOIN tbl_Lookup_Entity ON M.LEGAL_ENTITY = tbl_Lookup_Entity.LEGAL_ENTITY
        "GROUP BY M.BILLING_PERIOD, tbl_Lookup_Entity.Entity, IIf(Len(M![PRODUCT_CODE])<10,M![EMPLOYER_GRP_ID],IIf(InStr(8,M![PRODUCT_CODE],"-")>0,Mid(M![PRODUCT_CODE],InStr(5,M![PRODUCT_CODE],"-")+1,InStr(8,M![PRODUCT_CODE],"-")-6),Right(M![PRODUCT_CODE],9))), M.EMPLOYER, M.EMPLOYER_GRP_ID, M.PRODUCT_CODE, M.RISK;"


.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
That was an excellent idea.  We still have two errors however which I don't understand.  Can you also help here?
1)      " & rstTables.Fields(0) & "
Compile error:
Expected: end of statement

2)      " GROUP BY M.BILLING_PERIOD, tbl_Lookup_Entity.Entity, IIf(Len(M![PRODUCT_CODE])<10,M![EMPLOYER_GRP_ID],IIf(InStr(8,M![PRODUCT_CODE],"
Compile error:
Expected: line number or label or end of statement
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Rey Obrero (Capricorn1)Commented:
upload a copy of the db..
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Please see attached "CS_Invs_HWYS_Acctg TEST.accdb.'  Just so you know, the vba code is a "Sub cmdExport_Click" class module in 'frmDate Dialog 5500.'  It is there because I had a thought to use the form's text field as the year variant.  I was a thought but either way I want the code to work.

**The "qryAppend_ASO_Group_CS Memberlist" is the one I want to codify.

Also, I didn't paste the above revised code into the module because it failed to show as compilable.
CS-Invs-HWYS-Acctg-TEST.accdb
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Any thoughts on this?  I don't want it to fall through the cracks if possible.  Thanks.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
This is perfect.  THANKS SO MUCH!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.