Count only particular rows

[vae]

Have a problem which can't seem to get my head around.
I have to write a access query which will count how many times each different attribute has one particular value in one table.
So attribute [admission_id] which can be duplicated in this table will have a particular value [yes] or [no] in different rows and for example I have to count the total [yes]'s that appear for each different [admission_id]. Have tried DCount but this just returns the entire count of [yes]'s or [no]'s in the whole table instead for each particular [addmission_id]. ..Thanks.

 

[BobMcClellan]

>>I have to count the total [yes]'s that appear for each different [admission_id]
(not sure what your PK is but assume ID)
How about....

Select Id , AdmissionIdCnt = sum(iif([admission_id] = "Yes",1,0))
From YourTable
Group by ID

 

[vae]

Thanks for that Bob, I can see where that command line can come into it.
Doesn't seem to give me an answer though as keeps asking me to enter a parameter value for admission_idCNT but I think my original post was a little vague. I'll try to explain a little better

I have 5 attributes in one table. 2 of these are [admission_id] and [answer]and admission_ID is part of a composite PK. Now [admission_id] can have a value that can be duplicated on different rows in this table. The [answer] attribute has one value in each row either 'yes" or "no".
I need a query to count how many "yes's appear for each distinct admission_id value.

Thanks again

 

[BobMcClellan]

Vae, your error was due to me using...
AdmissionIdCnt = ( as opposed to AdmissionIdCnt: )
I write all my queries in SQL and forget about Access Aliasing names with a colon.

Let's try again. Does this get you there?

Select AdmissionId, AdmissionIdCnt: sum(iif([Answer] = "Yes",1,0))
From YourTable
Group by AdmissionId

 

[vae]

It works, fantastic, thanks Bob your a life saver

 

[BobMcClellan]

Glad to help Vae.