By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Best Way to Count Yes/No Fields?

twinnyfo
Expert Mod 2.5K+
P: 3,284
Accessors,

Even though this question may appear to be related to another thread, it is not. As I have been reviewing one of my projects for documentaiton purposes, I've come across some forms and queries which count Yes/No fields. For example, in my table of officers eligible form promotion, I have a Yes/No Field stating whether the officer was, in fact, selected for promotion.

Of course, there are many ways to skin any form of feline, but I am looking for the "best" way, if there is one.

In Queries, I could use:
Expand|Select|Wrap|Line Numbers
  1. Abs(Sum([Selected]))
in an aggregate query. As you can see, this method simply "sums" the Yes/No field (which will be either 0 for No or -1 for Yes), then takes the absolute value of that sum.

If I have a text box on a form, likewise, I can use a similar method:
Expand|Select|Wrap|Line Numbers
  1. =Abs(DSum("[Selected]", "tblEligibles"))
or, slightly modified:
Expand|Select|Wrap|Line Numbers
  1. =DCount("*", "tblEligibles", "[Selected]")
Of course, all methods return the same value.

I have no problems with any of these methods functioning properly, but was just wondering which method would be preferred over others in different situations. Also, for some strange reason, they all feel a bit clumsy--although admittedly, that may be because of my own self-imposed paradigm that one ought to be able to simply use
Expand|Select|Wrap|Line Numbers
  1. =Count("[Selected]", "tblEligibles")
and get the correct value.

Any thoughts from those who have deeper understanding of this?
Jul 5 '18 #1

✓ answered by NeoPa

Good instincts clearly Twinny.

Where you can, use the recordset already available. If you have a Query(Def) or a bound Form or Report, then you have a recordset already that has the information. Using a domain aggregate function in such circumstances would be unnecessary and wasteful. Possible. The waste would rarely be noticeable. Nevertheless it would be nooby and clumsy.

Furthermore, using anything but the recordset you already have would involve replicating the recordset accurately. Checking and duplicating the Filter property at the very least. A gaping opportunity to get it wrong and provide unreliable information. Not likely to result in any sort of pat on the back from above :-( (Not that I'd see that as your principle motive).

As for using Sum() instead of Count(), there is good logic in what you do. Nevertheless, it relies on a feature that is more accidental than logical. The value of True happens to be -1 when dealing with unsigned integers. There's a good reason for this, and it won't be changing any time soon, but it does mean anyone looking at the code will need to take that into his understanding when trying to decipher your code. On balance then, not an approach I'd recommend.

That leaves us with using Sum() or Count() in a way that's more explicit. I'm going to lean towards Sum() as getting Count() to work for you this way relies on converting False values to Null. Again, more confusing for a reader.

So, after all that, I'd recommend using :
Expand|Select|Wrap|Line Numbers
  1. Sum(IIf([Selected],1,0))
The Count() alternative would be :
Expand|Select|Wrap|Line Numbers
  1. Count(IIf([Selected],[Selected],Null))

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Good instincts clearly Twinny.

Where you can, use the recordset already available. If you have a Query(Def) or a bound Form or Report, then you have a recordset already that has the information. Using a domain aggregate function in such circumstances would be unnecessary and wasteful. Possible. The waste would rarely be noticeable. Nevertheless it would be nooby and clumsy.

Furthermore, using anything but the recordset you already have would involve replicating the recordset accurately. Checking and duplicating the Filter property at the very least. A gaping opportunity to get it wrong and provide unreliable information. Not likely to result in any sort of pat on the back from above :-( (Not that I'd see that as your principle motive).

As for using Sum() instead of Count(), there is good logic in what you do. Nevertheless, it relies on a feature that is more accidental than logical. The value of True happens to be -1 when dealing with unsigned integers. There's a good reason for this, and it won't be changing any time soon, but it does mean anyone looking at the code will need to take that into his understanding when trying to decipher your code. On balance then, not an approach I'd recommend.

That leaves us with using Sum() or Count() in a way that's more explicit. I'm going to lean towards Sum() as getting Count() to work for you this way relies on converting False values to Null. Again, more confusing for a reader.

So, after all that, I'd recommend using :
Expand|Select|Wrap|Line Numbers
  1. Sum(IIf([Selected],1,0))
The Count() alternative would be :
Expand|Select|Wrap|Line Numbers
  1. Count(IIf([Selected],[Selected],Null))
Jul 5 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 3,284
NeoPa,

Thanks for the sage advice (again). Beacuse I am using small(ish) numbers of records, I don't see any performance improvements, but I do see the logic and straightforward approach. I have "counted" other values in some queries using the IIf([Something], 1, 0), but just wasn't "seeing" that as an approach in my current situation.

Let me also say that over the past few weeks, I've had a lot of questions directed your way and the answers have really pushed me beyond my current comfort zone. I understand SQL/Queries better, Booleans better as well as having a better approach to how I do things.

As usual, many thanks for the insights you provide here on Bytes!
Jul 5 '18 #3

NeoPa
Expert Mod 15k+
P: 31,494
As with my earlier post in a separate thread Twinny, when I say it's a pleasure, I want you to understand how simple & true that is. Having someone who wants to learn is a joy in and of itself. You know that because I've seen you've experienced that yourself on here.

So, I happily accept your thanks, and send my own back to you.

Keep on doing what you're doing. You're making a big difference.
Jul 5 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.