Bowling scores statistics

[Skettiman51]

I am trying to write a query/report that will read the bowling scores from a table for several bowlers and calculate several things: Average, High Game, Total Games bowled, # of game 200 and over, # of games 250 and over, and # of 300 games. I have a query that reads the table and calculates Average, High Game, Total Games bowled for each of the bowlers. I also have a report that will print out that information too. But I would like to include the # of game 200 and over, # of games 250 and over, and # of 300 games. I have not be able to get a query to calculate all six fields. Any help will be much appreciated.

[June7]

Should provide your SQL statement.

The additional fields would be calculated like:

Sum(IIf(Score=>200, 1, Null))

Sum(IIf(Score=>250, 1, Null))

Sum(IIf(Score=300, 1, Null))

An alternative is to build report using Sorting & Grouping features with aggregate calcs in textboxes. This allows display of detail data as well as summary info.

[jojowhite]

from your Query (say the name of the query is Query1) that calculate the Average, High games, and Total games, create new query out of it"

SELECT BowlerName, 
           Average,
          [High game], 
          [Total Games],  
(SELECT Top 1 COUNT("1") FROM yourBowlingTable WHERE BowlerName = Query1.BowlerName And [Game Score] Between 200 And 249) As [# of Games 200 and over], 
(SELECT Top 1 COUNT("1") FROM yourBowlingTable WHERE BowlerName = Query1.BowlerName And [Game Score] Between 250 And 299) As [# of Games 250 and over],
(SELECT Top 1 COUNT("1") FROM yourBowlingTable WHERE BowlerName = Query1.BowlerName And [Game Score] >=300 ) As [# of Games 300] 
FROM Query1