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:

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>
[TH="bgcolor: #c0c0c0"] Year [/TH]
[TH="bgcolor: #c0c0c0"] MonthNumber [/TH]
[TH="bgcolor: #c0c0c0"] MonthName [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountNew [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountOld [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountRecert [/TH]

</tbody>

The table should look like this:

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>
[TH="bgcolor: #c0c0c0"] Year [/TH]
[TH="bgcolor: #c0c0c0"] MonthNumber [/TH]
[TH="bgcolor: #c0c0c0"] MonthName [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountNew [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountOld [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountRecert [/TH]

</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.

 

[cmcreynolds]

Thanks, Xenou-

1) there are not any NewCert records (that distinction started this year and we may have some come in next week) - but that's why I'm confused about the 1s in the months for Feb-Nov. I did not take out any records.
2) the start dates are reliable, I just created those thinking about translating "1" to "January" and "2" to "February" etc. If there's some other way to do that, I'll take that suggestion to, especially if it may be the cause of those pesky 1s.

 

[Joe4]

I had to remove the names and emails because of security (is that okay?)

That is fine (and recommended, we don't want to post anyone's real information!).
Those fields are not important to the query anyway.

So I copied your data and queries, and here is what I got:

2017	1	January	0	56	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>
[TH="bgcolor: #c0c0c0"] Year [/TH]
[TH="bgcolor: #c0c0c0"] MonthNumber [/TH]
[TH="bgcolor: #c0c0c0"] MonthName [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountNew [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountOld [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountRecert [/TH]

</tbody>

which is what I would suspect. I do not understand why you are getting different results (I cannot seem to reproduce it). Its really odd because the calculation is set up in exactly the same manner for those other two which seem to work fine.

Do we a favor and trying add a Recert or OldCred record for the month of June. Does the one in the NewCred line for June go away when a number is put in one of those other two columns? I am just trying to determine if the pattern is that all months will no data seem to get a one put in that first column.

A question regarding the MonthNumber and Year fields we are joining on. Are they set up as numbers in both tables? Or is one or both Text?

 

[cmcreynolds]

So, I can't add a record to our database (at this time), so I took the "BOGExam-Registered" query, made a table, then manually entered in the following row (for a total of 59):

123456	MCREYNOLDS, CLIFTON	email@email.com	MEMB	BOGEXAM	BOGHM		6/15/2017	Recert	6	2017

<tbody>
[TH="bgcolor: #c0c0c0"] ID
[/TH]
[TH="bgcolor: #c0c0c0"] LAST_FIRST [/TH]
[TH="bgcolor: #c0c0c0"] EMAIL [/TH]
[TH="bgcolor: #c0c0c0"] MEMBER_TYPE [/TH]
[TH="bgcolor: #c0c0c0"] ACTIVITY_TYPE [/TH]
[TH="bgcolor: #c0c0c0"] PRODUCT_CODE [/TH]
[TH="bgcolor: #c0c0c0"] DESCRIPTION [/TH]
[TH="bgcolor: #c0c0c0"] StartDate [/TH]
[TH="bgcolor: #c0c0c0"] INDICATOR [/TH]
[TH="bgcolor: #c0c0c0"] Month [/TH]
[TH="bgcolor: #c0c0c0"] Year [/TH]

</tbody>

I adjusted the query to reflect the table name:

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 INNER JOIN [Exam-Registered-DeleteLater] ON (MonthLookup.Year = [Exam-Registered-DeleteLater].Year) AND (MonthLookup.MonthNumber = [Exam-Registered-DeleteLater].Month)
GROUP BY MonthLookup.Year, MonthLookup.MonthNumber, MonthLookup.MonthName
ORDER BY MonthLookup.Year, MonthLookup.MonthNumber;

And got this as a result:

2017	1	January	0	56	1
2017	6	June	0	0	1
2017	12	December	0	1	0

<tbody>
[TH="bgcolor: #c0c0c0"] Year [/TH]
[TH="bgcolor: #c0c0c0"] MonthNumber [/TH]
[TH="bgcolor: #c0c0c0"] MonthName [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountNew [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountOld [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountRecert [/TH]

</tbody>

Which is closer to what I want - but I'd "like" the months to stay there, but say 0 across. Is this fixed because I queried a table instead of another query?

 

[cmcreynolds]

Oops - and yes, all months and years are numbers. Sorry, forgot to add that.

 

[Joe4]

Which is closer to what I want - but I'd "like" the months to stay there, but say 0 across. Is this fixed because I queried a table instead of another query?

No, look at your query again. When you adjusted it, you changed it from a LEFT JOIN to an INNER JOIN.
Remember, you must make it a LEFT JOIN to show all the records from your MonthLookup table.

 

[cmcreynolds]

Yep - I caught that join change apparently the same time.

So, I did get the table I want:

2017	1	January	0	56	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	1
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

<caption> Query1 </caption> <thead>
[TH="bgcolor: #c0c0c0"] Year [/TH]
[TH="bgcolor: #c0c0c0"] MonthNumber [/TH]
[TH="bgcolor: #c0c0c0"] MonthName [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountNew [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountOld [/TH]
[TH="bgcolor: #c0c0c0"] ExamRegistCountRecert [/TH]

</thead> <tbody>
</tbody> <tfoot></tfoot>

But does this mean the table vs query argument is it?

 

[Joe4]

But does this mean the table vs query argument is it?

Possibly. It is pretty rare, but I did come across an instance once in SQL where I was getting unexpected results. I was using multiple layers of queries, and I was getting errors because of invalid data. That data was being weeded out by the first query in line, but a query doing a calculation done the line was returning the error. It shouldn't have been, because the bad data was already weeded out by then. But then I learned that when you submit the query (which depends on other queries), SQL determine what the most efficient way of doing so is, so it may restructure things behind the scenes. I wonder if something like this may be what is going on...

Like I said, it is pretty rare. I have only come across that once in about 17 years.

One workaround would be to write your query to a table, and then work off of that. That obviously is not ideal...