Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data From Many to One Table - vba

Posted on 2015-01-02
5
Medium Priority
?
226 Views
Last Modified: 2015-01-05
I'm trying to pull data from many months into one table.  I've started with the vba code below but get bogged down when it comes to using the various source tables.  Please see query in attached database.  Ultimately I want data from both 201404 and 201405 tables into one 'tbl_Med-Drug_PREM' table with a column showing the net for each month.  The 'qSel_QS_PREM_SUM' is a sample from only one generic table but it shows the output with only one month's NET column.  

Below is the code.  I know the query works but it returns with a Run-time error '2342'.  " A RunSQL action requires an argument consisting of an SQL statement."

I think it's missing a "rstGroup" or some such that should be part of the run/movenext commands but don't know where it should be placed.  I've seen in my research that other errors can result if I place it wrong.

I'm hoping to get all months, each with its own net column but can't get through it with my limited vba skills.  Can someone please review the code I started to help me flesh out this little project?

Public Sub Quota_Share_Out()

Dim Db As DAO.Database
Dim rstTables As DAO.Recordset
Dim rstGroup As DAO.Recordset
Dim strSql As String
    Set Db = CurrentDb
    Set rstTables = _
      Db.OpenRecordset("tblPeriods", dbOpenTable)
     
rstTables.MoveFirst
Do While Not rstTables.EOF
    strSql = "SELECT Format([TDS_GB_ENT " & rstTables.Fields(0) & "]![BLAC_POSTING_DT],""yyyymm"") AS YYYYMM, [TDS_GB_ENT " & rstTables.Fields(0) & "].GRGR_ID, [TDS_GB_ENT " & rstTables.Fields(0) & "].GL_LGL_ENT_CD, " & _
    "Facets_QS_Gps_xls.[GRP NAME], IIf(IsNull([Facets_QS_Gps_xls]![GRP NAME]),""N"",""Y"") AS QS, [TDS_GB_ENT " & rstTables.Fields(0) & "].GL_CJA_CD, Sum([TDS_GB_ENT " & rstTables.Fields(0) & "].NET_AMT) AS NET " & _
    "FROM [TDS_GB_ENT " & rstTables.Fields(0) & "] LEFT JOIN Facets_QS_Gps_xls ON [TDS_GB_ENT " & rstTables.Fields(0) & "].GRGR_ID = Facets_QS_Gps_xls.GROUP " & _
    "WHERE (((Mid([TDS_GB_ENT " & rstTables.Fields(0) & "]![CSPI_ID],""7"",""1"")) Not In (""A"",""B"")) AND ((Left([TDS_GB_ENT " & rstTables.Fields(0) & "]![PDPD_ID],""1"")) Not In (""D"",""V"")) AND " & _
        "((Mid([TDS_GB_ENT " & rstTables.Fields(0) & "]![PDPD_ID],""4"",""1"")) Not In (""R"")) AND (([TDS_GB_ENT " & rstTables.Fields(0) & "].RATE_SIZE_IND) In (""5"",""6"",""7"",""8"")) AND (([TDS_GB_ENT " & rstTables.Fields(0) & "].ACGL_TYPE)=""P"")) " & _
    "GROUP BY Format([TDS_GB_ENT " & rstTables.Fields(0) & "]![BLAC_POSTING_DT],""yyyymm""), [TDS_GB_ENT " & rstTables.Fields(0) & "].GRGR_ID, [TDS_GB_ENT " & rstTables.Fields(0) & "].GL_LGL_ENT_CD, Facets_QS_Gps_xls.[GRP NAME], " & _
    "IIf(IsNull([Facets_QS_Gps_xls]![GRP NAME]),""N"",""Y""), [TDS_GB_ENT " & rstTables.Fields(0) & "].GL_CJA_CD;"
     
     Debug.Print strSql
     
'DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
rstTables.MoveNext
Loop

End Sub
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
5 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40528528
>I know the query works but it returns with a Run-time error '2342'.  " A RunSQL action requires an argument consisting of an SQL statement."

The fact that you're getting the error suggests that your query is not properly formulated.

If you take the output from your "Debug.Print strSql" line, does that execute if you paste it into the query builder?

How many tables do you have to loop through?

Why is your dynamic query a SELECT rather than an INSERT (if you are trying to build a summary table with the results of the queries)?
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 750 total points
ID: 40529214
I would suggest using single quotes within the SQL string, to avoid problems.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 750 total points
ID: 40530288
>>> Please see query in attached database

There is no database attached to your question.

You cannot "Run" a SELECT query.   DoCmd.RunSQL  requires an Action query, such as SELECT ... INTO, INSERT, UPDATE, DELETE, etc.
0
 
LVL 1

Author Closing Comment

by:CFMI
ID: 40532198
mbizup - Issue resolved with "You cannot "Run" a SELECT query."  Well I was trying to create an INSERT query. . . .OOPS.  When I changed it to

"INSERT INTO [tbl_Med-Drug_PREM] ( Period, Source, QS, GRGR_ID, [GRP NAME], GL_LGL_ENT_CD, GL_ACCT_NBR, GL_RSK_CD, GL_PRODT_CD, GL_MKT_CD, GL_CJA_CD, NET ) " & and added a "GROUP BY"

it all worked beautifully!

Helen_Feddema - I changed the double quotes to single and will note that for the future.

Thanks to all.
0
 
LVL 1

Author Comment

by:CFMI
ID: 40532215
Another mistake.  My apologies to SimonAdept.  I failed to note he also saw "a SELECT rather than an INSERT" and should have been given points.

I'm so sorry and will be sure to allocate better next time.  If I can redo it this time, please notify.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

722 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