Counting Cases Based on a Condition

[dwmatteson]

Hi folks --

I'm attempting to generate a report (based on a query) that counts the number of people in a family, and also breaks them down into adults (>=18 yrs old), children (<18 yrs old), and pre-teen children (<=12). Naturally, these last two categories are not mutually exclusive, but that's by design.

I'm able to count the number of people in the family without any trouble. The headaches begin when I try to count the number of people in each family by age groups. I've tried a variety of Count statements in different locations (in the report, in the query), but I just can't seem to wrap my brain around this one. I've not yet tried any VBA solutions, since I'm still learning, but I'm more than willing to give it a shot!

I appreciate any suggestions you can offer.

Thanks,

Don

 

[cmmrfrds]

You should be able to use 3 subqueries. Here is the general syntax, but you should check out help on subqueries. I put aliases after the table names to keep them clear. D is the outer query and A B C are inner queries. Depending on your needs you may need more criteria.

Select familyname,
(select count(familyname) from family A where age >= 18 and A.familyname = D.familyname) as adult,
(select count(familyname) from family B where age < 12 and B.familyname = D.familyname) as preteen,
(select count(familyname) from family C where age < 18 and C.familyname = D.familyname) as child
from family D
group by D.familyname

 

[dwmatteson]

Thanks for the quick response cmmrfrds,

I follow most of what you've suggested, but since I'm new to databases (and, by extension, SQL), there are a few things that I'm not following entirely. I've been through the Access help on subqueries and I've looked briefly through a couple of on-line SQL tutorials, but I still have a some questions.

I'll try to go through your suggestion step-by-step to see if I understand what I think I understand:

SELECT FAMILYNAME,

Selects the FAMILYNAME field from some source to be specified later.

(SELECT COUNT(FAMILYNAME)

Begins a subquery in which it's counting the number of records that match each FAMILYNAME

FROM FAMILY B

Specifies the source for the subquery as being FAMILY B. Here's where I get lost. What is this record source? The table with all of the family members? The table with the family records? A separate query?

WHERE AGE >=18 AND B.FAMILYNAME = D.FAMILYNAME)

Limits the returned records to those aged 18 or older with FAMILYNAMEs in the subquery that match the FAMILYNAME in the &quot;main&quot; query.

AS ADULT

This appears to provide the subquery with a handy name. As you said, an alias.

Okay. This happens a couple more times for children and preteens, and then:

FROM FAMILY D

This is the record source for the &quot;main&quot; query, no? Again, would this be the table containing information about family members, or would it be the table with family information?

GROUP BY D.FAMILYNAME

This sorts and groups records by the FAMILYNAME. Once again, D.FAMILYNAME would be the FAMILYNAME field in which table? (Sorry I'm being dense about this.)

Finally, I'm still a bit fuzzy on the inner/outer query distinction, but that's more a matter of reading that stuff a few more times and pounding it into my head, I think. If anybody would like to expound on those, though, that'd be okay.

Again, a billion thanks in advance. You folks are great!

Don

 

[dwmatteson]

Okay. I've run off and taken an online introductory SQL course and it all makes much more sense to me now. Thanks for your help! (If there are any fellow SQL/Access newbies out there who want to know how that query translates into plain English, let me know and I'll post an annotated explanation.)

I haven't had a chance to try it out, but I have a good idea of where to go if it doesn't.

Thanks again,

Don