Having Issue With COUNT in a Query

[Derrick0690]

Good Morning,

I am trying to COUNT how many times a value of 0 comes up in a series of 4 fields. Not sure how to get this right.

Here is the table format that I am working with.
Athlete_ID Scores_Date Game_1 Game_2 Game_3 Game_4
Busch 6/4/12 200 199 187 211
Prybys 6/4/12 187 222 199 175
Furtney 6/4/12 178 167 166 200
Busch 6/12/12 199 222 223 199
Prybys 6/12/12 234 200 199 0
Furtney 6/12/12 189 175 0 0

Here is the query that I need to build.
Athlete_ID Scores_Date Total_Pinfall Number_Games_Bowled Average

I need to calculate each athletes total pinfall, number of games bowled, and average for each date. I have been able to figure out how to get the total pinfall and the average. However, since the number of games beign bowled per athlete on any given date is not set in stone I need to be able to calculate an accurate average.

For instance, based on the above table, the results in the Number_Games_Bowled column should be as follows:
Athlete_ID Scores_Date Total_Pinfall Number_Games_Bowled Average
Busch 6/4/12 4
Prybys 6/4/12 4
Furtney 6/4/12 4
Busch 6/12/12 4
Prybys 6/12/12 3
Furtney 6/12/12 2

What do I need to do in order to get these results? Any help is appreciated!



Regards,
Derrick0690

[June7]

This is marked as solved. Do you still need assistance?

The solution should be an expression in query, like:

IIf(Game_1=0,0,1) + IIf(Game_2=0,0,1) + IIf(Game_3=0,0,1) + IIf(Game_4=0,0,1)