1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Count number of occurences in Access?

Discussion in 'Business Applications' started by mterry, May 19, 2005.

Thread Status:
Not open for further replies.
  1. mterry

    mterry Thread Starter

    Jun 13, 2004
    I need to count the number of times each value is used in a field in a query, any idea how to do this?
    For example, I have a column called Training_Score with values 1,2,3,4,5 available. How do I count the number of 5s, 4s, 3s, etc.?

  2. Gregor1234


    Jun 3, 2004
    Simple and quick:

    Create your query as though you were interested in viewing the field in question.

    Below the field of interest, it in the "Criteria" row type in "=4" to see all occurances of four or "=5" to see all fives or whatever you want. (Of course you will leave the double quotes off when you do this. I just put them there for this explanaton.)

    Now look up on the Access tool bar and find the button with "Summation Sign" this is a Greek letter Sigma and looks like a real funky "E". Click the Sigma and a new row called "Total" will appear. The words "Group By" will be displayed in that row. Use the drop down box to change the "Group By" to "Count" and run the query. It will count the number of occurances four or five of whatever value you put in the Criteria row.

    Better and more sophisticated:

    Create a query to display the field in question but put that field in twice... that's two columns each showing the field of interest.

    Now click the Sigma button to show the "Total" row. Leave the total row as "Group By" in the first column and set it to "Count" in the second column. Set the "Sort" row to "Ascending" in the first column (only).

    Run the query and it will display each unique value found in the field along with how many times it occurs.
  3. mterry

    mterry Thread Starter

    Jun 13, 2004
    Thanks for the info. Now I need to know how to count the number of each value for each field.
    Example: FieldA can have values 1-5, field B can have 1-5, field C can have Yes/No. These values are unrelated to each other (row 1 has values 5-3-Yes, row 2 has values 3-3-No, row 3 has 1-1-Yes). I need to know how many times each value was selected from the available values for that field. I will be placing this in the end either in a spreadsheet in Excel or a report in Access.
  4. AKast.com


    May 28, 2005
    If you are going to put the data in excel anyway, the problem can be solved using the COUNTIF fomulae.


    Number of times Answer is 4:

    Where C2:C17 is the range of values you want to check.

    Using this formula you can count all the responses for each column.

    Kind Regards

    Support Team

    Owner: A Kingsbury, 9 Castledine St Extension, Loughborough LEICS, LE11
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/363698