Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Count number of occurences in Access?


(!)

mterry's Avatar
mterry mterry is offline
Member with 39 posts.
THREAD STARTER
 
Join Date: Jun 2004
Experience: Intermediate
19-May-2005, 10:02 AM #1
Solved: Count number of occurences in Access?
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.?

TIA,
Marge
Gregor1234's Avatar
Gregor1234 Gregor1234 is offline
Member with 237 posts.
 
Join Date: Jun 2004
Experience: Intermediate
19-May-2005, 12:52 PM #2
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.
mterry's Avatar
mterry mterry is offline
Member with 39 posts.
THREAD STARTER
 
Join Date: Jun 2004
Experience: Intermediate
27-May-2005, 01:27 PM #3
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.
AKast.com's Avatar
AKast.com AKast.com is offline
Junior Member with 21 posts.
 
Join Date: May 2005
Experience: Einstein
28-May-2005, 11:47 AM #4
Lightbulb Counting occurences
If you are going to put the data in excel anyway, the problem can be solved using the COUNTIF fomulae.

i.e.

Number of times Answer is 4:
=COUNTIF(C2:C17,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
AKast
www.AKast.com

AKast
Owner: A Kingsbury, 9 Castledine St Extension, Loughborough LEICS, LE11
2NT
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑