Reporting a count of number of records that meet the criteria

[cbarnett]

Hi,

I would like to report a count of the number of students who meet certain criteria.



e.g.
I have a table with the following

Student Name
Class
SEND
IBP
PPI


I've grouped them into Classes but now would like to report how many students are PPI (at the moment it is filled with either a 1 for yes or 0 for no) or have an IBP (either a 2 for yes or 0 for no.

Can I report a count as to how many 1's there are?

thanks

[Welshgasman]

Use DCount() ?

[cbarnett]

Thanks - unfortunately, it's just giving me an error message. I'm not sure about what to put as the domain.

[CJ_London]

unfortunately we are not clairvoyant - suggest provide the code you have created and the detail of the error message.

Your original post is unclear - you say

PPI (at the moment it is filled with either a 1 for yes or 0 for no) or have an IBP (either a 2 for yes or 0 for no.

but you want

Can I report a count as to how many 1's there are?

so what is the relevance for this?

IBP (either a 2 for yes or 0 for no.

since you want 1's why include IBP which does not have any 1's?

[Gicu]

I'd say you should change the PPI and IBT to be both Yes\No fields (you can make them to display a check-box if you want) then in a totals query you can group by Class and sum the two fields using PPI_Students:ABS([PPI]),IBP_Students:ABS([IBP]).
Cheers,

[June7]

Not necessary for field to be Yes/No type to do group and sum or count.

There are arguments against use of Yes/No fields. http://allenbrowne.com/NoYesNo.html

[Gicu]

I am aware of that article but for most cases the yes\no fields are working OK (when used as intended with default property set). And what Allen doesn't mention is that they do have an advantage over the number fields in that they come with built-in validation (they can only take true or false or -1 and 0) while for a number field you will have to build that yourself (the OP mentions 2 or True in IBP)...

Cheers,

[CJ_London]

(the OP mentions 2 or True in IBP)

actually, OP said

either a 2 for yes or 0 for no.

The issue I have with yes/no fields is you cannot store a null - you can get the situation where you want 'don't know' as an option, so using a number (I tend to use byte) is a way round that,

[Gicu]

True=Yes?
And yes, there is the rare case when you need a Null but for most cases the Yes\No alternatives are enough. To dismiss the usage of the entire data type for very few specific reasons is my my opinion a bit excessive, but that's just me...

The issue I see in this particular case is trying to add those values; for IBT the OP used 2 to mean yes (or True), maybe for a third field in the future he would use 3 for yes. So you will have replace a simple sum with a count where IBT<>0 or similar plus having to enforce at the table\form level the proper value for each field (so PPI only takes 1 or 0, IBT takes 2 or 0 and so on).

Anyway the OP seems to be busy at the moment so we'll see if what comes back if anything.

Cheers,