cmcreynolds
Active Member
- Joined
- May 21, 2015
- Messages
- 295
Hello again everyone -
Again, I'm used to PowerQuery/PowerPivot and I am needing some of that functionality.
This is only a part of a larger (but not that large) report that I'm building in Access 2013.
I'm trying to summarize and UNIQUELY count member IDs but as it splits across months (I would do this in PowerPivot using CountAX or if I summed, SUMX). I know Access and PowerPivot are two different software, but I was hoping to find something similar.
First, does DCount in Access not "uniquely" count? If I'm using it wrong, then my second question won't make sense-
Second, I have a simple table with the month number and month names because my report would have all 12 months listed, even if it was only run in January (not my choice).
Here is the SQL from this query:
But, when I run the query, it repeats the totals for every month:
<tbody>
</tbody>
The table should look like this:
<tbody>
</tbody>
Can anyone help me figure out how to correctly utilize DCount (if that is, in fact, what I should use)?
Thank you in advance.
Again, I'm used to PowerQuery/PowerPivot and I am needing some of that functionality.
This is only a part of a larger (but not that large) report that I'm building in Access 2013.
I'm trying to summarize and UNIQUELY count member IDs but as it splits across months (I would do this in PowerPivot using CountAX or if I summed, SUMX). I know Access and PowerPivot are two different software, but I was hoping to find something similar.
First, does DCount in Access not "uniquely" count? If I'm using it wrong, then my second question won't make sense-
Second, I have a simple table with the month number and month names because my report would have all 12 months listed, even if it was only run in January (not my choice).
Here is the SQL from this query:
Code:
SELECT [BOGExam-Registered].Year, MonthLookup.MonthNumber, MonthLookup.MonthName, DCount("ID","BOGExam-Registered","Indicator='NewCred'") AS ExamRegistCountNew, DCount("ID","BOGExam-Registered","Indicator='OldCred'") AS ExamRegistCountOld, DCount("ID","BOGExam-Registered","Indicator='Recert'") AS ExamRegistCountRecert
FROM [BOGExam-Registered] RIGHT JOIN MonthLookup ON [BOGExam-Registered].Month = MonthLookup.MonthNumber
GROUP BY [BOGExam-Registered].Year, MonthLookup.MonthNumber, MonthLookup.MonthName
ORDER BY MonthLookup.MonthNumber;But, when I run the query, it repeats the totals for every month:
| Year | MonthNumber | MonthName | ExamRegistCountNew | ExamRegistCountOld | ExamRegistCountRecert |
|---|---|---|---|---|---|
| 2017 | 1 | January | 0 | 53 | 1 |
| 2 | February | 0 | 53 | 1 | |
| 3 | March | 0 | 53 | 1 | |
| 4 | April | 0 | 53 | 1 | |
| 5 | May | 0 | 53 | 1 | |
| 6 | June | 0 | 53 | 1 | |
| 7 | July | 0 | 53 | 1 | |
| 8 | August | 0 | 53 | 1 | |
| 9 | September | 0 | 53 | 1 | |
| 10 | October | 0 | 53 | 1 | |
| 11 | November | 0 | 53 | 1 | |
| 2017 | 12 | December | 0 | 53 | 1 |
<tbody>
</tbody>
The table should look like this:
| Year | MonthNumber | MonthName | ExamRegistCountNew | ExamRegistCountOld | ExamRegistCountRecert |
|---|---|---|---|---|---|
| 2017 | 1 | January | 0 | 53 | 0 |
| 2 | February | 0 | 0 | 0 | |
| 3 | March | 0 | 0 | 0 | |
| 4 | April | 0 | 0 | 0 | |
| 5 | May | 0 | 0 | 0 | |
| 6 | June | 0 | 0 | 0 | |
| 7 | July | 0 | 0 | 0 | |
| 8 | August | 0 | 0 | 0 | |
| 9 | September | 0 | 0 | 0 | |
| 10 | October | 0 | 0 | 0 | |
| 11 | November | 0 | 0 | 0 | |
| 2017 | 12 | December | 0 | 0 | 1 |
<tbody>
</tbody>
Can anyone help me figure out how to correctly utilize DCount (if that is, in fact, what I should use)?
Thank you in advance.
