Results 1 to 3 of 3
  1. #1
    Derrick0690 is offline 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. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,473
    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.
    Attached Files Attached Files

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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 09-21-2012, 11:12 AM
  2. Combine values from multiple fields
    By jsimard in forum Queries
    Replies: 8
    Last Post: 06-09-2011, 01:05 PM
  3. Look up values in multiple table fields
    By nmcentire in forum Programming
    Replies: 2
    Last Post: 11-12-2010, 02:02 PM
  4. Counting across multiple fields
    By shak2 in forum Programming
    Replies: 37
    Last Post: 10-20-2010, 10:20 AM
  5. Excluding null values on SQL table query
    By DB2010MN26 in forum Queries
    Replies: 1
    Last Post: 09-03-2010, 12:54 PM

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