Calculate the number of Families that fall into certain income levels
This is a query that lets you calculate the number of families that fall into certain income level and groups them by family size. It selects the cases based upon Date Opened.
The query, with minor changes can be used to count just about anything you need.
This is a custom query so you must have the ability to use the full version of Access.
- Go to the top Ribbon Bar and choose Create. Then Select Query Design.
- Choose ClientsW as the Table to Add and close the Show Table box
- In the first column type in
[Adults] + [Children]
This provides us with the family size.
- When you move to the second column, Access will put Expr1: in front of the above. Type over Expr1: and change it to Family Size:
- For your second column on the Field row type
IIF([Tincome] <= 600 or [Tincome] = Null, 1, 0)
- When you move to the next column the Expr2: will appear next to the above. Change it to something more meaningful, such as
Inc <=600:
- For your third column on the Field row type
IIF([Tincome] >= 601 or [Tincome] <= 1200, 1, 0)
Now change the label as above to
Inc 601-1200:
- When you’ve repeated the above for the number and amounts of the ranges
you need, add a column that says
DOPEN.
On the Criteria line under DOPEN, type:
Between [Enter Beginning Date Open] and [Enter Ending Date Open]
- Press the Sigma Button (Totals) at the top of the screen.
- Change the GROUP BY to SUM under each of your formulas, but leave the
first column saying Group By for your Family Size.
- Change the Group By to WHERE under your DOPEN column.
Run the query to see how many families fall into each income range for the date open range you enter.
REMEMBER: You can use the immediate if (IIF) for a combination of conditions. For example:
IIF([Sex] = “F” AND [Age] >=60, 1, 0) would count the number female clients who are 60 or older.
You can use this to group people by ages, look at clients living in certain cities, etc.