Solved

Looped SQL for Complex VBA Query

Posted on 2014-03-27
7
342 Views
Last Modified: 2014-04-10
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
0
Comment
Question by:CFMI
  • 4
  • 3
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39960187
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
 
LVL 1

Author Comment

by:CFMI
ID: 39961547
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39961562
upload a copy of the db..
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:CFMI
ID: 39961725
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
 
LVL 1

Author Comment

by:CFMI
ID: 39983115
Any thoughts on this?  I don't want it to fall through the cracks if possible.  Thanks.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39983952
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
 
LVL 1

Author Closing Comment

by:CFMI
ID: 39992312
This is perfect.  THANKS SO MUCH!!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now