Count if?

[antinora]

Greetings:



I am an experienced Excel user and newer access user. I've had success using Expression builder to create query fields for calculations and logical issues the populate reports with calculated fields.

My recent frustration involves counting student exams types (math, science, history) based on passing score.

I need to count exams greater than or equal to 65. The issue involves counting:

English exam >=65
Global History exam >=65
US history exam >=65
Biology Or Geology Or Chemistry Or Physics >=65 (4 possible exams to choose from since students may take any one of these exams to satisfy graduation requirements)
Algebra Or Geometry Or Trigonometry >=65 (3 possible exam to choose from)

I've tried various bracket combinations with Or statements for the Math & Science options but keep receiving errors.

In Excel I've always used Or(Algebra>=65, Geometry>=65, Trigonometry>=65) in combination with AND statements.

How can this be done with Expression building or am I completely off base when counting items in Access?

Any direction would be greatly appreciated.

Thanks, JA

[JoeM]

OR and AND do exist in Access, but are structured differently in Access.

In Excel, it is structured like this: OR(condition1, condition2,...)
In Access, it is structured like this: (condition1) OR (condition2) ...

Most commonly, they are used with an Immeditate IF statement. This is like an IF statement in Excel (structured the same way), but is spelled IIF.

Hopefully, that will help you build your statement. If you have trouble, post your formula attempt here and we will help you clean it up.

[antinora]

Wow Joe! Thanks for the prompt reply. I did figure out the OR condition being put in between each field name. I guess my issue is nesting OR and AND together.

I will post my formula for your review soon.

Thanks,

JA

OR and AND do exist in Access, but are structured differently in Access.

In Excel, it is structured like this: OR(condition1, condition2,...)
In Access, it is structured like this: (condition1) OR (condition2) ...

Most commonly, they are used with an Immeditate IF statement. This is like an IF statement in Excel (structured the same way), but is spelled IIF.

Hopefully, that will help you build your statement. If you have trouble, post your formula attempt here and we will help you clean it up.

[antinora]

Joe:

Here is my expression. Depending on the how many parenthesis I use or where I put them, I am getting either a "too many closing parenthesis error" or "query does not include specified expression".
Thanks for any help you may provide...JA

5RequiredRegents: Count([ENG REG]>=65) And ([GLOBAL REG]>=65) And ([US HIST REG]>=65) And (([LIVING ENV REG]>=65 Or [EARTH SCI REG]>=65 Or [CHEMISTRY REG]>=65 Or [PHYSICS REG]>=65)) And (([ALGEBRA REG]>=65 Or [GEOMETRY REG]>=65 Or [Trig REG]>=65))

[antinora]

Basically I need to count any of those exams (fields) if they are >=65

[JoeM]

The COUNT function is an Aggregate Function that you would use in an Aggregate (Totals) Query. If you are just trying to count the number for a single record, you could use a series of IIF functions (returning a 1 or 0) and adding them together, or just use boolean logic which return a -1 for True and 0 for False, then use the ABS (absolute value) function to make the negative positive, i.e.

5RequiredRegents: Abs(([ENG REG]>=65) + ([GLOBAL REG]>=65) + ([US HIST REG]>=65) + (([LIVING ENV REG]>=65) Or ([EARTH SCI REG]>=65) Or ([CHEMISTRY REG]>=65) Or ([PHYSICS REG]>=65)) + (([ALGEBRA REG]>=65) Or ([GEOMETRY REG]>=65) Or ([Trig REG]>=65)))

[NoellaG]

Hi,

I assume you have a 3-table structure like tblPupils, tblExams and tblExamsResults where you put which pupil has which result for which exams. I would say: just query the tblExamsResults for >= 65, and count the results per pupil. I can't see why you should use difficult formula's.

greetings
Noëlla