Solved

Looped SQL for Complex VBA Query

Posted on 2014-03-27
7
344 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

939 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

6 Experts available now in Live!

Get 1:1 Help Now