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.