Results 1 to 3 of 3
  1. #1
    DonCarlos is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    2

    Using SQL: Similar Code Using SUM and GROUP BY Resulting in Different Structured Output

    OS: Windows 7
    Access: 2010 32-Bit

    I want to calculate the percent of records complete by year, query date of the report, quarter, and region separately on two different tables producing two tables based on the results. Similar code is applied separately on each table, but the resulting tables are quite different in resulting format.

    Table 1:
    Code:
    dbs.Execute ("CREATE TABLE RegionPercCompleteAll([Accountable Office Desc] VARCHAR, [Fiscal Year] TEXT(4), [QueryDate] VARCHAR, [Quarter] TEXT, SumRecordCount NUMBER, SumRecordsWithActuals NUMBER, PercentComplete NUMBER);")
    
    
    strSQL1 = "INSERT INTO RegionPercCompleteAll([Accountable Office Desc], [Fiscal Year], QueryDate, Quarter, SumRecordCount, SumRecordsWithActuals) SELECT DISTINCT TPA.[Accountable Office Desc], TPA.[Fiscal Year], TPA.QueryDate, TPA.Quarter, SUM(TPA.RecordCount) AS SumRecordCount, SUM(TPA.RecordsWithActuals) AS SumRecordsWithActuals FROM TPAPercCompleteAllR AS TPA GROUP BY TPA.[Fiscal Year], TPA.QueryDate, TPA.Quarter, TPA.[Accountable Office Desc];"
    
    dbs.Execute (strSQL1)
    Table 2:
    Code:
    dbs.Execute ("CREATE TABLE RegionPercCompleteExclO([Accountable Office Desc] VARCHAR, [Fiscal Year] TEXT(4), [QueryDate] VARCHAR, [Quarter] TEXT, SumRecordCount NUMBER, SumRecordsWithActuals NUMBER, PercentComplete NUMBER);")
    
    strSQL2 = "INSERT INTO RegionPercCompleteExclO([Accountable Office Desc], [Fiscal Year], QueryDate, Quarter, SumRecordCount, SumRecordsWithActuals) SELECT DISTINCT TPA.[Accountable Office Desc], TPA.[Fiscal Year], TPA.QueryDate, TPA.Quarter, SUM(TPA.RecordCount) AS SumRecordCount, SUM(TPA.RecordsWithActuals) AS SumRecordsWithActuals FROM TPAPercCompleteExclOR AS TPA GROUP BY TPA.[Fiscal Year], TPA.QueryDate, TPA.Quarter, TPA.[Accountable Office Desc];"
    
    dbs.Execute (strSQL2)
    Both source files have 10,028 records of the exact same structure and data types. The difference is that the individual record counts for the source file for Table 2 have one extra condition applied to exclude some records of specific type.

    Both Source Files are structured as follows:

    AccountableOffice (Text)
    Year (Text)
    QueryDate (Text)
    Quarter (Text)
    TPRID (Text)
    TPR Desc (Text)
    Record Count (Number)
    RecordswithActuals (Number)
    PercentComplete (Number)

    Sample Output:
    Table 1: (340 records)
    Accountable Office Desc Fiscal Year QueryDate Quarter RecordCount RecordsWithActuals PercentComplete
    Central 2014 28/07/2016 20936 12700 60.66
    East 2014 28/07/2016 22472 13527 60.19
    Central 2014 Qtr1 28/07/2016 5234 2526 48.26
    East 2014 Qtr1 28/07/2016 5618 2557 45.51
    Table 2: (400 records)

    Accountable Office Desc Fiscal Year QueryDate Quarter RecordCount RecordsWithActuals PercentComplete
    Central 2014 28/07/2016 Qtr1 1602 1283 80.09
    East 2014 28/07/2016 Qtr1 1843 1426 77.37


    Each report aggregates differently, Table 1 has several records that aggregate results by accountability office, year and querydate but add up for all quarters. Table 2 is different in that it follows the correct structure, but repeats the results three times according to different sorts with not ORDER BY applied to either tables.

    Internet searches did not help to try and answer why the two output tables are so different.

    Thank you in advance for your help in seeing my coding error.


  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Why are you using vb code rather than just executing sum queries?

  3. #3
    DonCarlos is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    2
    Good question.

    In my circumstance, I am jumping from Excel to work on a project where I want to automate building of various analysis tables off one main database table in one set of code. The main table is a collection of consistently structured Cognos reports of transactions taken on different years and at different query dates. So the above mentioned table is one among various tables being built to try and glean different angles upon the information.

    Actually, I am not all that familiar with MS Access, but am more comfortable with relational databases, SQL and VBA. (And I am limited to software installed on work computers.) Although, I don't know how to do a query using the menus, I can readily automate what information I want to capture using VBA.

    Since posting my question, I learned that the code is correct. My issue was that I did not remove old records from the two tables each time the code is run such that some of the records in the two reports were products of previous incomplete or incorrect SQL coding.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 09-07-2018, 04:59 PM
  2. Replies: 2
    Last Post: 04-26-2016, 05:24 PM
  3. Access group/ungroup similar to excel
    By lou788 in forum Access
    Replies: 3
    Last Post: 03-04-2015, 06:19 PM
  4. Group Text Box to output in columns and not rows
    By Fuzz_cozens in forum Reports
    Replies: 1
    Last Post: 02-28-2014, 10:30 AM
  5. Group By Query - no zero values in the output
    By jpvonhemel in forum Queries
    Replies: 15
    Last Post: 12-12-2012, 12:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums