?
Solved

Looped SQL for Complex VBA Query

Posted on 2014-03-27
7
Medium Priority
?
373 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …
Suggested Courses

749 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