I am creating a database for collecting statistical information for a college bowling team. I need some help in counting records based on the values in mutiple fields.
The database includes many tables and relationships. There is a table for the frame information that has a bowler ID and a tournament ID in it. This table has a one-on-one relationship based on the bowler ID and tournament ID to a table called "Tournament Totals". The Tournament Totals table is where I want to summarize the statistics.
I am counting information like total frames bowled, first ball average, total strikes, total spares, times the head pin was knocked down, etc.. I want to be able to count to number of single pin spares attempted and made. I am able to count the number of single pin spares attemped by counting the records where 9 pins where knocked down on the first ball.
What I cannot figure out is how to count the number of records where 9 pins where knocked down on the first ball and the spare was made. I do have fields in the frame scoring table for shot1count and a switch for marks (1=Strike, 2=Spare). I want to do this for when 8 pins where knocked down and 7 pins knocked down as well.
I also want to keep track of attempts and spares for particular pin combinations, but I am pretty sure the same calculation will work for that.
Thanks for any assistance anyone can provide.