DSum/DCount - Number of Unique records (1 Viewer)

jack555

Member
Local time
Today, 20:29
Joined
Apr 20, 2020
Messages
93
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.

CategoryItemNameMonth
FruitsApple2021-08
FruitsOrange2021-08
VegChilli2021-08
VegOnion2021-08
SnackBiscuit2021-08
SnackChips2021-08
FruitsApple2021-08
FruitsOrange2021-08
VegChilli2021-08
VegOnion2021-08
VegChilli2021-08
VegOnion2021-08
FruitsApple2021-09
FruitsOrange2021-09
VegChilli2021-09
VegOnion2021-09
SnackBiscuit2021-09
SnackChips2021-09
SnackBiscuit2021-09
SnackChips2021-09

Desired Result

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

Category2021-082021-09
Fruits
2​
1​
Veg
3​
1​
Snack
1​
2​

Or

CategoryMonthCount
Fruits2021-08
2​
Veg2021-08
3​
Snack2021-08
1​
Fruits2021-09
1​
Veg2021-09
1​
Snack2021-09
2​

My Access expertise - beginner
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:29
Joined
Oct 29, 2018
Messages
21,358
Have you tried a Totals query or a Crosstab query?
 

jack555

Member
Local time
Today, 20:29
Joined
Apr 20, 2020
Messages
93
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.
 

June7

AWF VIP
Local time
Today, 08:29
Joined
Mar 9, 2014
Messages
5,423
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;
 
Last edited:

jack555

Member
Local time
Today, 20:29
Joined
Apr 20, 2020
Messages
93
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

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
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

AWF VIP
Local time
Today, 08:29
Joined
Mar 9, 2014
Messages
5,423
I tried that before posting answer. Does not produce desired output.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
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

Member
Local time
Today, 20:29
Joined
Apr 20, 2020
Messages
93
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

AWF VIP
Local time
Today, 08:29
Joined
Mar 9, 2014
Messages
5,423
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

Member
Local time
Today, 20:29
Joined
Apr 20, 2020
Messages
93
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

Super Moderator
Staff member
Local time
Today, 12:29
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom