Query and Reporting Tips & Tricks
Aggregating Demographic Data in a Single Query or Report
I get this a lot in nonprofit applications, especially for demographic data. Let's say you have a field that stores Race, and also a series of checkboxes for Disabled, Homeless, etc. The client wants a report that "counts" the number of people in each group and shows it on the same report.
It is easy to do any one of these in an Access query -- you just group by that field and count the ID. But to do them all in a single report, you need to get clever. Create a query with fields like this:
TotalWhite: Sum(-([Race]=’White’)) TotalNonwhite: Sum(-([Race]<>’White’)) TotalDisabled: Sum(-([Disabled])) TotalHomeless: Sum(-([Homeless])) TotalRepublican: Sum(-([Republican])) Interesting: Sum(-([Republican] Or [Homeless] Or [Disabled] Or [Race]<>’White’)
This takes advantage of the fact that True is -1, while False is 0. The great thing is, you can go further to Group By another field, such as County or City or Program or School, thereby analyzing the statistics for each of the groups in a single query or report.