Count Records Based on Field Value

[mozz29]

Hello,

I am working with an Access 2013 database supplied by a program I use at work. No one here knows how to use Access...After hours of tutorials, I've successfully made a query to pull the needed information from the supplied table. There is just one final thing I'd like to add but can't figure out how.

There is a field in the table where the value can be -1,0,1,or 2. I would like to count the number of records where this field=2. I've tried several Count IIf and Sum IIf expressions that I've seen suggested when people have posted similar questions. The results have either been that the query will run but all the fields (including previously working ones) come out blank, or the query won't even run and I get an error message about the expression being typed incorrectly or being too complex. It seems like a pretty simple thing but I'm totally stuck and desperate.

Thanks in advance to anyone with ideas!

[PhilOfWalton]

Suppose your table is called "MyTable" and your field is called "MyField"

Then this SQL should give you what you want

SELECT Count(MyTable.MyField) AS CountOfMyField
FROM MyTable
WHERE MyTable.MyField = 2;
 

Or Simpler still

SELECT Count(MyField) AS CountOfMyField
FROM MyTable
WHERE MyField = 2;
 

Phil

[NeoPa]

If you want these aggregate values AS WELL AS individual record values then you really need to consider the sense of what you're after. It's actually quite difficult to get a count (or any other aggregation) of records or values while showing individual values too. Possible, but difficult.

What exactly is it that you're after?

[mozz29]

Thanks for the replies, both of you. Finally had time to work on this again. I've been doing everything in design view because I'm not familiar with SQL, so I wasn't sure how to incorporate this into the existing query's SQL. I made a new query with that SQL to see what it would look like in design view and then tried adding that to the existing query. While it runs successfully on its own in a separate query, when added to the existing query it will run without an error but give a blank datasheet.

To answer NeoPa's question, I'm working with a testing program that saves the participants' results to this Access database. Each question in the test corresponds with a record in the table, so for every test completed by each applicant there are roughly 20-30 records. One of the fields records the location of their response as either -1, 0, 1, or 2. I currently have a query that returns all the tests from a given participant, along with information like how many questions were completed and how many were correct. I'm trying to add information about biases towards specific locations, which is why I'm trying to figure out how to add this count of records where the field=2.