Count of two different fields in same query (1 Viewer)

[mackyrm]

I have a query with a number of events. Each event has multiple entries, with two TRUE/FALSE fields of interest. I would like to count each of these two fields and summarise in one query for each of the events.

i.e. a list of events with a count of committed to attend and those who actaully did.

I plan to have a third column - the diffrence between the two.

When I do a count on both fields, I get the same numbers in each, when I know they are different and the count is the same when I query them individually . The criteria selection is true, but I believe the query is simply counting all records, regrdless of criteria.

Advice appreciated!

 

[raskew]

Hi -

It would seem that if you are 'counting' existing fields, you'd have to come up with equal numbers. Perhaps you need to be summing the fields, but it's hard to tell with the information provided. It'd be helpful if you could provide a sample db.

Best wishes - Bob

 

[Brianwarnock]

As Bob says it is difficult to comment from the info given. Your Sql might have helped.
Also Bob is correct that to count multiple fields in 1 query you usually need to use a Sum(iif(criteria,1,0) thus giving a 1 when what you want to count is true and then summing them.

If you have boolean True/False and you wish to count the True then abs(sum(field)) should do it.

Brian

 

[EraserveAP]

Sounds to me like they want to add each field to a query and set both fields to count?

 

[Brianwarnock]

Sounds to me like they want to add each field to a query and set both fields to count?

Did you read POST#1

When I do a count on both fields, I get the same numbers in each,

A count counts the number of records in a group, including or excluding blankss dependent on which form is used, as no field is blank he will get all records for each count.

Brian

 

[EraserveAP]

You can count both fields in one count. It depends on your requirements and what they contain.
In my count version 1 you will find that The Paid Field contains a total of 15, The Use Field contains a total of 23 out of 26 records.
I use similar counts like this routinely.
In count version 2 I Included both fields to Count twice and Both as Group By to see the individual Break Downs and how they add up to their Totals. Once again the numbers differentiate.

So yes it is entirely possible depending on what you have and what you want to do.
As I did read the post, seeing as how that would be required to answer on topic, it lacks complete information. Given that, I made a guess as to what the OP wanted.

 

[Brianwarnock]

As expected you are counting all none blanks or all records, my response to that is in my previous post.

Brian

 

[EraserveAP]

As expected you are counting all none blanks or all records, my response to that is in my previous post.

Brian

Of course I am counting non-blanks, I did not say otherwise. I simply stated you can count two fields in one Query.
As in Version two in my sample you get a break down of all those non-blank records, which is why I made it.

As I previously stated, it depends on what you want and what you got. If he/she is willing to make changes he/she can even use the first query and disregard the second one.
For example if you need a specific count of people in The US and you want 2 states broken out all you need to do is add two fields and respectively tag 1 state per field. You can then count the two fields and get a total for the two states and the US. Count of STATE1, Count of STATE2, Count of US.
Works perfectly fine for that purpose which is why I created Version 2, it is similar in function.
I never once stated any query would do contrary to what you previously stated.

 

[Brianwarnock]

But your counts of paid =yes are within the grouping of USE.
I have added a 3rd query to show you the difference.

Brian

 

[EraserveAP]

Quite true. Thank you for showing me the 3rd query.

 

[Brianwarnock]

Your welcome, both methods have their uses, its just a pity the original poster has never been back to learn the various Count options that we have discussed. His last visit to the forum was his post.

Brian

 

[Dennisk]

far easier to have each count in a seperate query the bring all the counts together in one final query. been doing this way since 1992, never had a problem.

 

[Brianwarnock]

We will have to disagree on this. The Sum(IIF( approach allows you to have counts of any number of fields and values in fields all in one simple query, useful for counting people in age ranges.

Brian

 

[Dennisk]

Brian,

It just goes to show that when you hit on a technique that works, you tend to use it forever. Next time I have to develop queries for age ranges I will try the SUM method.