2 Replies Latest reply on Jul 29, 2010 9:13 AM by JeanneLorang

    Brain fried - simple(?) calculations for a dog show

    JeanneLorang

      Title

      Brain fried - simple(?) calculations for a dog show

      Post

      Hi- Noob back again. I think these are simple, but I am having no luck. My brain is just fried from being at a computer for 5 days straight. I want to create two basic calculations.

      In the first example, I have a field called height in cm in one table (evaluations). In a second related table (entry info), I have fields for sex (Dog/Bitch) and an entry type field (puppy /adult). I want to get an average of the height for all dogs that are  male, excluding puppy, then also do the same for females, excluding puppy. So each calculation formula should produce a single #, the average height for all the males, etc. I have 6 measurements to do this for, but once I know the formula, I should be good.

      Second example, I need to do a count of the number of fields that meet two criteria. For example, for coat quality I have 4 possible ratings, EX, VG, G, F. I need to know how many males recived an EX, How many VG, etc,  and the same for females. I can include puppies in this set. Here I have 43 qualities to count for each sex, but once I know the formula, I can adapt. In this case, ratings are in my evaluations table, the other info is in my entry info table. The two tables are related by entry #, a field I won't be using, of course, if that matters.

      I know this is simple (or at least I hope), but I have gotten nowhere. I've never worked with scripts, so haven't even looked if there is a simple way to do all the calculations at once.

      Thanks so much for any help - I'm missing something obvious, I know. If I had more time / less deadline, I might be able to figure it out!

        • 1. Re: Brain fried - simple(?) calculations for a dog show
          philmodjunk

          You have two options for computing an average drawn from a group of records.

          1. Use an "average of" summary field. If you perform a find to exclude records you don't want (such as puppys) you can place this field in a grand summary, header or footer layout part to see the average for all the records in your found set. You can also place this field in a sub Summary part and get the average for a sub group of the total set of groups.
          2. Use the Average function. This is an aggregate function and if you set up a table with a relationship that matches only to the group of records you want to average, Average ( relatedtable::measurementfield ) will compute the average. You could thus set up a table with one record for each group that you want an average of with fields that select a specific group of dogs and can then get averages for each.

           

          To count how many "EX" ratings, use a calcualtion like this:  (CoatQuality = "EX") + (Conformation = "EX") + .....

          CoatQuality = "EX" returns 1 if this is true and 0 if it is not so adding these expressions together will count the number of fields that were rated "EX".

          Note:  Given the number of such ratings, it might simplify your system to put each rating in a related table instead of using so many dedicated fields. Your table might be structured like this:

          entry #
          RatingCategory
          Rating
          ExFlag : Rating = "EX"

          Then Sum ( RatingsTable::ExFlag ) would count the number of EX ratings a dog would receive.

          • 2. Re: Brain fried - simple(?) calculations for a dog show
            JeanneLorang

            Thanks so much for the help - I knew I was missing something basic! I did end up doing some of  it by hand, took less time than setting up tables / fields for a one time project. But it got done.