Microsoft Access - distinct count - Asked By Eric on 24-May-12 09:48 AM

Hi all,

I’m trying to count the total number of different ItemID from table Records, grouped by month. The query below calculates the total but not the DISTINCT ItemID’s. I found several related posts on the web but can’t manage to get my query working properly.

Can anyone please help?

Many thanks!

Eric

PS. To make it more complicated, is there a way to also include null values in the result? (if there are no ItemID’s in a particular month)

SELECT Format([Records].[Date],"yyyy/mm") AS [Month], Count(Records.ItemID) AS NumItemID

FROM Records

GROUP BY Format([Records].[Date],"yyyy/mm");

Robbe Morris replied to Eric on 24-May-12 02:51 PM

SELECT Records.ItemID, Format([Records].[Date],"yyyy/mm") AS [Month], Count(*) AS CNT

FROM Records

GROUP BY Records.ItemID, Format([Records].[Date],"yyyy/mm");

Pat Hartman replied to Eric on 26-May-12 11:57 PM

This is a two part query. You can use a sub query but I find it easier to use two separate queries. The first gets the distinct items for the period and the second counts them.
qry1:

SELECT Records.ItemID, Format([Records].[Date],"yyyy/mm") AS YearMonth

FROM Records

GROUP BY Records.ItemID, Format([Records].[Date],"yyyy/mm");

qry2

Select YearMonth, Count(*) As CountDistinctItems

From qry1;

Eric replied to Pat Hartman on 28-May-12 09:33 AM

Thank you Pat, this works!
Eric