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.