[mterry]

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]

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]

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]

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