DSum/DCount - Number of Unique records

[jack555]

I would like to generate the number of category occurrences in a particular month for the below example table. I think it can be done via DSUM/DCOUNT but couldn't able to achieve. Please help.

Category	ItemName	Month
Fruits	Apple	2021-08
Fruits	Orange	2021-08
Veg	Chilli	2021-08
Veg	Onion	2021-08
Snack	Biscuit	2021-08
Snack	Chips	2021-08
Fruits	Apple	2021-08
Fruits	Orange	2021-08
Veg	Chilli	2021-08
Veg	Onion	2021-08
Veg	Chilli	2021-08
Veg	Onion	2021-08
Fruits	Apple	2021-09
Fruits	Orange	2021-09
Veg	Chilli	2021-09
Veg	Onion	2021-09
Snack	Biscuit	2021-09
Snack	Chips	2021-09
Snack	Biscuit	2021-09
Snack	Chips	2021-09

Desired Result

The month is represented in two columns, but even in the same colum is ok.

Category	2021-08	2021-09
Fruits	2​	1​
Veg	3​	1​
Snack	1​	2​

Or

Category	Month	Count
Fruits	2021-08	2​
Veg	2021-08	3​
Snack	2021-08	1​
Fruits	2021-09	1​
Veg	2021-09	1​
Snack	2021-09	2​

My Access expertise - beginner

 

[theDBguy]

Have you tried a Totals query or a Crosstab query?

 

[jack555]

Have you tried a Totals query or a Crosstab query?

I tried crosstab and total query without success. I could able to produce the total number of records, not the occurrences of category. I might be doing something wrong.

 

[Gasman]

They say a Picture is worth a thousand words

https://www.youtube.com/results?search_query=access+crosstab+query

 

[June7]

It's not really clear what constitutes an 'occurrence'. Why does Snack show 2 for 2021-08?

Consider:

Query1:
SELECT Data.Category, Data.ItemName, Data.Month, Count("*") AS Cnt
FROM Data
GROUP BY Data.Category, Data.ItemName, Data.Month;

Query2:
SELECT DISTINCT Query1.Category, Query1.Month, Query1.Cnt FROM Query1;

 

[jack555]

It's not really clear what constitutes an 'occurrence'. Why does Snack show 2 for 2021-08?

Consider:

Query1:
SELECT Data.Category, Data.ItemName, Data.Month, Count("*") AS Cnt
FROM Data
GROUP BY Data.Category, Data.ItemName, Data.Month;

Query2:
SELECT DISTINCT Query1.Category, Query1.Month, Query1.Cnt FROM Query1;

Yes, this solves my problem. Thank you very much.

PS: updated the question to correct wrong info about "snack"

 

[June7]

I tried that before posting answer. Does not produce desired output.

 

[jack555]

You can do this with a single query:

SELECT Data.Category, Data.Month, Count("*") AS Cnt
FROM Data
GROUP BY Data.Category, Data.Month;

Do NOT include columns you want to summarize away.

June7 was answering a different question which was what your words said. I answered the question using your suggested answer.

This counts all the records of Data.Category. I wish Category should be counted only once for each group of Data.ItemName.

Suggestion by @June7 works fine. out of curiosity, I am thinking about how to make this work in a single query.

 

[June7]

Can nest the queries for one SQL statement.

SELECT DISTINCT Query1.Category, Query1.Month, Query1.Cnt FROM
(SELECT Data.Category, Data.ItemName, Data.Month, Count("*") AS Cnt
FROM Data
GROUP BY Data.Category, Data.ItemName, Data.Month) AS
Query1;

 

[jack555]

Can nest the queries for one SQL statement.

SELECT DISTINCT Query1.Category, Query1.Month, Query1.Cnt FROM
(SELECT Data.Category, Data.ItemName, Data.Month, Count("*") AS Cnt
FROM Data
GROUP BY Data.Category, Data.ItemName, Data.Month) AS
Query1;

Exactly. my problem solved.