Count number of items in a query

[AZKing]

Hi,

I am creating a query where I would like to count the number of specific items in a certain column of another query. I can do this for one item, for example:

SELECT DISTINCTROW[Item List].[Discussion Item], Count([Item List].[State]) AS [Delivered Items]
FROM [Item List]
WHERE (([Item List].[Discussion Item])="Yes") AND (([Item List].[State])="DELIVERED")
GROUP BY [Item List].[Discussion Item];

But, how do I do this for multiple items?

Thanks for your help!!

[PEB]

Hi,

In fact you can do it with subqueries:

In a query based on your information source

And in the field row you can put:

Count1:(SELECT DISTINCTROW Count([Item List].[State]) AS [Delivered Items]
FROM [Item List]
WHERE (([Item List].[Discussion Item])="Yes") AND (([Item List].[State])="DELIVERED") AND (([Item List].[Discussion Item])=[My_source].[Discussion Item] )
GROUP BY [Item List].[Discussion Item])

:)

Have a nice day!

[AZKing]

Thank you very much, it worked great!!