help with the count function

[uapak90]

Once again, i am stuck with a function.
Let me show a sample of the data then I'll go from there.

Date Room/Location ColonyCount
01/02/08 Compounding 0
01/03/08 Ointment 2
01/04/08 Compounding 3
01/05/08 Liquid 0
01/06/08 Compounding 1

What I want to do is, make a query, grouping them by room/location and finding out which colony count was more than 2, since that's a significant number.
So say at the end, with the above information I would want to ssee someting like

Room/Location # of Significant Count
Compounding 1
Ointment 0
Liquid 0

Compounding has one since one of its counts were 3.
I made a query like this:

SELECT [Rodac Plates (2008)].[Room/Location], (Count(IIf([Count]>2,1,0))) AS [# of Above Alert]
FROM [Rodac Plates (2008)] INNER JOIN [Rodac Plates] ON ([Rodac Plates (2008)].[Room/Location] = [Rodac Plates].[Room/Location]) AND ([Rodac Plates (2008)].Schedule = [Rodac Plates].Schedule) AND ([Rodac Plates (2008)].Date = [Rodac Plates].Date)
GROUP BY [Rodac Plates (2008)].[Room/Location]
HAVING ((([Rodac Plates (2008)].[Room/Location])="Compounding" Or ([Rodac Plates (2008)].[Room/Location])="Liquid" Or ([Rodac Plates (2008)].[Room/Location])="Ointment"));

But it says data type mismatch... and im not evne sure if thats the right way to go

 

[randy700]

How about...

SELECT Rodac.Room, Count(Rodac.ColonyCount) AS Alert
FROM Rodac
GROUP BY Rodac.Room
HAVING Count(Rodac.ColonyCount)>2

Randy

 

[uapak90]

randy, your code works but it does not give me the results I want, I want to count how many of the samples have 2 or more counts but less than 3. The code you had kind of counts everything, and in the data I have, none of the data was equal to 2, infact everything was 0. And if there is no count, than I want the value to be 0. This was the code I put in :

SELECT [Rodac Plates (2008)].[Room/Location], Count([Rodac Plates (2008)].Count) AS [# of Above Alert]
FROM [Rodac Plates (2008)]
GROUP BY [Rodac Plates (2008)].[Room/Location]
HAVING (((Count([Rodac Plates (2008)].Count))>=2));

 

[randy700]

In your original post, you stated that results you wanted would show Compounding 1. Using the same sample data, the query I provided results in Compounding 1.

Unless you have decimals in your data, how do you expect to get MORE THAN 2 and LESS THAN 3?

Apparently, I don't understand what you're trying to achieve.


Randy

 

[uapak90]

I forgot to add that there are two things,
Alert is when the count is 2 but less than 3
Action is when the count is 3 or more

If I figure out Alert, I can do action, but the code you gave me, was giving me a value like 20, which was the amount of times I entered Compounding in my data. Basically it was counting all the counts, not abiding by the criteria of being equal to or greater than 2