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"

 

[Pat Hartman]

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.

 

[June7]

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

 

[Pat Hartman]

Is Data.Month formatted so you are not seeing the actual value? That is the only thing that explains why the single query doesn't work.

 

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

 

[Pat Hartman]

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

That isn't what your example shows.

Access SQL doesn't have the command that will do this in a single query so you need two which is where June was going.

June just gave you a single query solution which is the same as the two query solution except one is nested within the other. This isn't a better solution for Access since Access doesn't optimize sub queries well. If you don't have a lot of rows, the sub query won't be a problem but if you do, the two query solution will offer better performance.

What you really wanted was for item to be counted once per category. This requires summarizing by category, Item in the inner query. Then you can count the distinct items for a category.