How to count check boxes??

[AnnaZ]

I have a table that contains the following fields:
township
male - yes/no
female - yes/no
number of kids

I need to create a query that will give me the count of males and females and sum of number of kids - all grouped by township.

I have created 3 separate queries that calculate each part and they work. My question is how do I display the results of all these queries in one report, all grouped by townships?

Or if there's a way to create one query that will add all of this up? when I tried to create one query, the check boxes were not calculated properly, because Access did not distinguish between filled and empty checkboxes and would just count them all...

Any ideas or suggestions would be really appreciated.

 

[DanG]

Try this...1 query

SELECT Township.Township, Sum(Abs([Male])) AS M, Sum(Abs([Female])) AS F, Sum(Township.NumKids) AS SumOfNumKids
FROM Township
GROUP BY Township.Township, Township.Male, Township.Female
HAVING (((Township.Male)=Yes)) OR (((Township.Female)=Yes))
ORDER BY Township.Township;

You can count "no" as 0 or no and "yes" as -1 or Yes in your queries.
Then I use the ABS() to make them real numbers so I can count them.

Hope this is what you need.

 

[snoko]

You don't need a Chkbox for both Male and female. It's the same thing, if they're not male then they must be female !

 

[ScottGem]

I partially agree with snoko. You should only have ONE field in your table named Gender (or Sex). To have separate fields in the table constitutes a repeating group which violates normalization rules.

However, you can have an Option Group on your form with 2 controls for either Male or Female. But if you do that it changes the way you count.

In that case, I would use either a Group By query to count the genders or use a Dcount function.

 

[Pat Hartman]

Domain functions do not belong in queries. Use the cooresponding aggregate function and join two queries if necessary. For example, if you need to calculate a percentage, you'll need a query that uses count or sum to aggregate the domain and you would join to that query to calculate the percentage.

Once your table contains more than a few hundred rows, you will see just how inefficient domain functions are in queries. Think about it - the domain function needs to run a separate query for each row in the recordset. Joins are far more efficient.

 

[ScottGem]

Domain functions do not belong in queries.

I agree, Sorry I didn't make it clear that a Group by query OR a Dcount should be used.

 

[AnnaZ]

Thanks for everyone's help!
DanG's query worked beautifully! The only thing it does not group male and female counts into one record. For example, if there are 2 males and 3 females in Maine township, what it shows is this:

2 males; 0 females; maine township
0 males; 3 females; maine township

I was wondering if there is a way to show this:

2 males; 3 females; maine township

And I do need male and female to stay as two separate checkboxes.

thanks!

 

[ScottGem]

Actually you don't need to keep them separate. Their being separate is what is causing your problem. You need to group on the town and then sum the ONE field in 2 columns:

Males: SUM(IIF([Gender] = 1,1,0))
Females: SUM(IIF([Gender] = 2,1,0))

This assumes that Gender holds a 1 for Male and a 2 for Female. You do your separation in your query not in the table.

 

[Pat Hartman]

By having two checkboxes you have the additional problem of someone checking both and you need to prevent that. If you want to show separate choices on the form, display the one SexCode as an option group. That solves the problem nicely since only one value can be selected and only one value is stored. Scotts suggested code will work just fine except that I would have made female = 1 and male = 2

 

[ScottGem]

except that I would have made female = 1 and male = 2

But 2 is twice as good as 1

 

[Pat Hartman]

That's a good point

 

[ChrisO]

Careful Scott…

Anyway, what is it about sex that implies only two possible states when the legal definition may require four?
Male, female, not stated or pending.

1. Male (Reasonably understood.)
2. Female (Never understood.)
3. Not stated (We should not even attempt to understand.)
4. Pending (Apart from the High Court, who cares?)

Looks like a violation of atomic (anatomical?) data.