Networking

Use a calculated field in a query to sum up Access check box data

If you are collecting data on a certain population, here's how to convert your the check box fields in your table to the information you need by using Access to add up all the answers and compare them at a glance.

If you are collecting data on a certain population, you may have a number of check box fields in your table. After conducting your surveys, the next step would be to use Access to add up all the affirmative answers and compare them to the sum of the negative answers.

For example, you ask your customers whether they intend to purchase a new wireless router in the next six months. To find the results, create a calculated field query by following these steps:

  1. Open the database containing the answers to your survey questions.
  2. In the Database window under Objects, click Queries.
  3. Click the Design button and then click OK.
  4. Select the table with the Will Purchase Router check box field.
  5. Click Add and then click Close.
  6. Click the first cell of the Query grid.
  7. Enter the following in the cell: SUM(Abs([Will Purchase])).
  8. Double-click Expr 1 and type Will Purchase Router In Six Months.
  9. Click in the next blank cell of the Query grid.
  10. Enter the following in the cell: SUM([Will Not Purchase]+1).
  11. Double-click Expr 1 and type Will Not Purchase Router In Six Months.
  12. Save and close the query.

When you run the query, the number of people who said they would purchase a wireless router appears in the first column; the number of those who said they would not purchase a router appears in the second column.

Miss an Access tip?

Check out the Microsoft Access archive, and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox