4 Replies Latest reply on Oct 3, 2012 5:14 AM by mikebeargie

    Count records Based on Multiple Fields


      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.

        • 1. Re: Count records Based on Multiple Fields

          In your frame information table, you could make a calculation field called 9pinspareindicator that looked something like this:


          if ( ball1 = 9 AND ball2 = 1 ; "1" ; "0" )


          This would give you a field that you can summarize (instead of count) through a relationship to give you your total (IE, if a bowler had 3 nine pin spares, his10 frames would be 1,1,1,0,0,0,0,0,0,0 in that calculated field for the 10 frame information records related to the bowler. You could then "sum" this field through a relationship to get 3 on the other side).


          If you wanted to do a more complex calculation, you could use a case() statement to calculate a spareIndicator field, IE:


          case (

             ball1 = 9 and ball2 = 1 ; "9pinspare" ;

             ball1 = 8 and ball2 = 2 ; "8pinspare" ;

            ball1 = 7 and ball2 = 3 ; "7pinspare" ;

            ball1 = 6 and ball2 = 4 ; "6pinspare" ;

            ball1 = 5 and ball2 = 5 ; "5pinspare" ;

            ball1 = 4 and ball2 = 6 ; "4pinspare" ;

            ball1 = 3 and ball2 = 7 ; "3pinspare" ;

            ball1 = 2 and ball2 = 8 ; "2pinspare" ;

            ball1 = 1 and ball2 = 9 ; "1pinspare" ;

             "not a spare"



          Hope this helps to put you in the right direction.

          • 2. Re: Count records Based on Multiple Fields

            Also, didn't see the date of your original post, hope this was still relevant...

            • 3. Re: Count records Based on Multiple Fields

              Hello Mike,


              I want to ask your help to improve my database design, an EMR for Optometrists in our locality.

              For each patient record, I have created a layout with 50 tabs representing the date of the visit of the patient on that day.

              That means, if a patient makes his eyeglasses one a year, the tabs will be useful for the Optometrist for 50 years.

              But if a patient makes a visit every 6 months , it will be usefull only for 25 years.

              You can just imagine that the tabs run across the entire monitor in it's length.

              I know that my design is quite funny but I don't know how to solve this.

              All I need is that for every patient, he will have unlimited number of visits on his record.


              Please help me.


              Thanks a lot and God bless.

              • 4. Re: Count records Based on Multiple Fields

                Hi Kroywen19. You should only post relevant topics inside of threads, if you're interested in asking someone for help or consultation privately, you should go to that person's profile and choose "send private message"