2 Replies Latest reply on May 21, 2009 6:27 PM by comment_1

    Need help creating a complex summary table!



      Need help creating a complex summary table!


      I am building a database for a clinical study comparing two ethnic groups. For each study participant, a large number of charcateristics are collected: age, gender, height, weight, medication use, etc. At completion of the study, I need to generate various summary statistics, depending on the nature of the variable. For some, this means average +/- standard deviation; for others, absolute count of occurences. All this has to be broken down into 2 categories: ethnic group 1 v. ethnic group 2.


      Some fields are text fields (e.g. Gender is "Male" or "Female", or Using Aspirin is "Yes", "No" or "Unknown"), others are number fields (e.g. Age, Height, Weight), and others still are Calculation fields (e.g. my database calculates clinical scores based on a number of variables, and the score may be "0", "1", "2", etc.).


      Now, then, how do I, on a single layout, generate a table that might look like this:


                            Ethnic Group 1                  Ethnic Group 2

      Age                    45 +/- 2.4                        44 +/- 3.1

      Male                      56%                                 50%

      Using ASA

         Yes                     67%                                30%

          No                     30%                                 70%

          Unknown              3%                                  0%


      I found the Summary Field for calculation of Average and Std Deviation, and can get the value for Ethnic Group 1 by performing a "Find Ethnic Group 1" on the database, but I have not found out how to display teh data for both groups side by side on the same layout. Also, I have not been able to find out how to count the number of occurrences for each item in a pop-up list (e.g. "Yes", "No", Unknown")...


      Can anyone help me with this? Thanks in advance.

        • 1. Re: Need help creating a complex summary table!

          I can't give you detailed advice without knowing more about how you've structured your data in tables and what relationships you already have. I can describe a general technique that might work.


          There are a number of aggregate functions you can look up in the help file. These can calculate sums, standard deviation and count records much like your summary fields, but they use relationships to match a specific set of records instead of relying on a table's found set to determine which records to include in the aggregate calculation.


          It may turn out that you can create a new table where each record stores keys that match specific subgroups of records as well as calculation fields that use the above aggregate functions to compute and display the values you need. Each record, in your case, might relate to records for a different age group.

          • 2. Re: Need help creating a complex summary table!

            SebT wrote:

            I have not found out how to display teh data for both groups side by side

            Filemaker is not very good at side-by-side. If you want to keep it simple, do your comparisons vertically, e.g.


            Ethnic Group 1

            • Age:  45 +/- 2.4
            • Male: 56%
            • ...

            Ethnic Group 2

            • Age:  44 +/- 3.1
            • Male: 50%
            • ...


            This is very easy to do by creating a sub-summary part when sorted by group.