Solved

Looped SQL for Complex VBA Query

Posted on 2014-03-27
7
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39961562
upload a copy of the db..
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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