List how many records have each value in a field
Often you will want a list of how many records have each value in a table. For example:
- How many members of each ethnicity are in our database?
- How many families are in each family group?
- How many contributions came from check, credit card, cash, or other?
To create a query that counts the total number of each value, go through the following steps:
- In Query Conditions, query for all the records you are looking for (example: all Active members, all registered families, all contributions).
- In Result Columns, add the column on which you want to report (example: ethnicity, family group, contribution type).
- In Result Columns, add the exact same column a second time, so that you see two identical result columns.
- Move the mouse over the second row. It will turn grey. There will be a button with an f to the right.
- Select the f button and choose "Count" from the menu that opens up. The second result column will update to say "Count of ..."
- Move the mouse over the first column. You will see an arrow button to the left.
- Select the arrow button and choose "Ascending". This will put the results in order.
- Click "Execute Query."
You will get a list of categories along with the number of records in that category.
3767 Ranchero Drive, Suite 100 | Ann Arbor, MI 48108 | Toll-Free 866.930.4774 | Fax 734.205.1011