Access Issues DCount

[cmcreynolds]

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:

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.

 

[Joe4]

I think you may want to use the Aggregate Function COUNT instead of DCOUNT.
Your query looks a little odd because typically you see either the use of an Aggregate Query OR functions like DCOUNT, but not both blended in the same query.

How you query needs to be written, I cannot say for sure because I don't know what the data in your data tables used in your query looks like.

 

[cmcreynolds]

Would it help to post a few rows of the other table?

ID	LAST_FIRST	EMAIL	MEMBER_TYPE	ACTIVITY_TYPE	PRODUCT_CODE	DESCRIPTION	StartDate	INDICATOR	Month	Year
385087	Sith, Manny	email@email.com	MEMB	BOGEXAM	ACHE		1/4/2017	OldCred	1	2017
396501	Leia, Princess	email@email.com	MEMB	BOGEXAM	BOGHM		1/24/2017	OldCred	1	2017
639219	Hammill, Mark	email@email.com	FELL	BOGEXAM	BOGHM		1/24/2017	Recert	1	2017
654907	Rickton, Mary	email@email.com	MEMB	BOGEXAM	BOGHM		12/31/2017	OldCred	12	2017
658262	Keffler, Nick	email@email.com	MEMB	BOGEXAM	BOGHM		1/13/2017	OldCred	1	2017
713959	Stark, Tony	email@email.com	MEMB	BOGEXAM	BOGHM		1/2/2017	OldCred	1	2017

<caption> BOGExam-Registered </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>

 

[Joe4]

Would it help to post a few rows of the other table?

Yes, please.

 

[cmcreynolds]

The other table is just a list of month numbers in one column and the month word names in the other.

 

[Joe4]

Try this:

SELECT MonthLookup.Year, MonthLookup.MonthNumber, MonthLookup.MonthName, Sum(IIf([Indicator]="NewCred",1,0)) AS ExamRegistCountNew, Sum(IIf([Indicator]="OldCred",1,0)) AS ExamRegistCountOld, Sum(IIf([Indicator]="Recert",1,0)) AS ExamRegistCountRecert
FROM MonthLookup 
LEFT JOIN [BOGExam-Registered] 
ON (MonthLookup.MonthNumber = [BOGExam-Registered].Month) AND (MonthLookup.Year = [BOGExam-Registered].Year)
GROUP BY MonthLookup.Year, MonthLookup.MonthNumber, MonthLookup.MonthName
ORDER BY MonthLookup.Year, MonthLookup.MonthNumber;

 

[cmcreynolds]

This resulted in 1s in the months with no data (Feb-Nov)

Year	MonthNumber	MonthName	ExamRegistCountNew	ExamRegistCountOld	ExamRegistCountRecert
2017	1	January	0	56	1
2017	2	February	1	0	0
2017	3	March	1	0	0
2017	4	April	1	0	0
2017	5	May	1	0	0
2017	6	June	1	0	0
2017	7	July	1	0	0
2017	8	August	1	0	0
2017	9	September	1	0	0
2017	10	October	1	0	0
2017	11	November	1	0	0
2017	12	December	0	1	0

<caption> Query1 </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>

 

[Joe4]

That is not what I got. This is what it showed for me, based on your example, which looks correct.

Year	MonthNumber	MonthName	ExamRegistCountNew	ExamRegistCountOld	ExamRegistCountRecert
2017	1	January	0	4	1
2017	2	February	0	0	0
2017	3	March	0	0	0
2017	4	April	0	0	0
2017	5	May	0	0	0
2017	6	June	0	0	0
2017	7	July	0	0	0
2017	8	August	0	0	0
2017	9	September	0	0	0
2017	10	October	0	0	0
2017	11	November	0	0	0
2017	12	December	0	1	0

<tbody>
</tbody>

If you copied my code, verbatim, and are not getting expected results, I suspect maybe a data issue.

 

[cmcreynolds]

Yeah, I copied it verbatim and still get 1s as above. And luckily the BOGExam-Registered is only 58 records and I can scan and there are no "NewCred" in any records (in the INDICATOR field). All the records are in January 2017 (with the one exception for December 2017, which is a data input error, but I felt it helped me know how things were progressing in my table).

I'm using Access 2013, is that what you're using? I am wondering if there's something in my settings that is causing the 1s in the months with no data?

 

[Joe4]

I am using Access 2010, so I do not expect that to be an issue.

Can you create a small sample data set that returns this problem and post it here?
Then I will recreate your exact data and see what happens on my side.