# Counting Values Across Multiple Fields While Excluding Any Zeros

1. Novice
Windows 7 32bit Access 2007
Join Date
Mar 2012
Posts
4

## Counting Values Across Multiple Fields While Excluding Any Zeros

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. Please be advised I do not know any code. I only know how to use the expression builder.

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 256 199 200 Prybys 6/4/12 188 199 206 211 Furtney 6/4/12 234 199 193 187 Busch 6/12/12 211 199 188 256 Prybys 6/12/12 199 199 199 Furtney 6/12/12 187 279

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 being bowled per athlete on any given date is not set in stone I need to be able to calculate an accurate average. I will be using 0 as a placeholder for those who do not bowl all games on a given date.

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 855 Should Be 4 =Total_Pinfall / Number_Games_Bowled Prybys 6/4/12 804 Should Be 4 =Total_Pinfall / Number_Games_Bowled Furtney 6/4/12 813 Should Be 4 =Total_Pinfall / Number_Games_Bowled Busch 6/12/12 854 Should Be 4 =Total_Pinfall / Number_Games_Bowled Prybys 6/12/12 597 Should Be 3 =Total_Pinfall / Number_Games_Bowled Furtney 6/12/12 466 Should Be 2 =Total_Pinfall / Number_Games_Bowled

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

Regards,
Derrick0690

2. Firstly, I would restructure your database to be normalized. I would have two tables.

PlayerTable
PlayerID (PK)
Player

GameTable
GameID (PK)
PlayerID (FK)
ScoresDate
GameNr
Score

Once you have this populated with your data, then you can run an aggregate query as follows

Code:
```SELECT tblPlayer.Player, tblGames.ScoreDate, Count(tblGames.Scores) AS CountOfScores, Sum(tblGames.Scores) AS SumOfScores, Avg(tblGames.Scores) AS AvgOfScores
FROM tblPlayer INNER JOIN tblGames ON tblPlayer.PlayerID = tblGames.PlayerID
GROUP BY tblPlayer.Player, tblGames.ScoreDate;```
An example is attached. Also look at the Relational Database articles in my signature block. They will help you to underestand normalization which is key in relational databases.

3. Novice
Windows 7 32bit Access 2007
Join Date
Mar 2012
Posts
4
Thank you very much. This solved this issue.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums - Senior Forums