4 Replies Latest reply on Dec 2, 2013 9:19 AM by Kj

    Advice for many counts on one page

    Kj

      Title

      Advice for many counts on one page

      Post

           I provided many counts using summary fields and (sorted) sub summary reports but the results are on a dozen different pages (each page sported by that category). What is wanted is a 'snapshot' with all of the counts on a single page, so the summary fields will not work. Yet I need counts and totals for a dozen different categories. 

           Records may or may not have a value in any of (12) category fields. When there is a value, the categories will have a value from 1 to 10. 

           The desired result is vertically, 1 through 10 on the left side and then the dozen categories horizontally across the top, and those counts separated by gender.

           I am working on it but I am looking for the best or a better way and would appreciate advice. Thank you.

        • 1. Re: Advice for many counts on one page
          philmodjunk

               The main complication is that you have 12 category fields in the same record. What is the purpose to having 12 different fields for the category? Does this mean that the same record can be a member of up to 12 different categories?

          • 2. Re: Advice for many counts on one page
            Kj

                 Yes, that is the complication. There are 12 different fields on each record with no value or a value from 1 to 10 in each of the 12 fields.

            • 3. Re: Advice for many counts on one page
              philmodjunk

                   I suggest that you need a more flexible data model (tables + relationships).

                   Instead of 12 different category fields, you could set up some related tables like this:

                   YourTable----<Category_Join>----Categories

                   YourTable::__pkTableID = Category_Join::_fkTableID

                   Categories::__pkCategoryID = Category_Join::_fkCategoryID

                   You would have 12 records in Categories, one for each category. To assign a record in YourTable to a category, you create a new record in Category_Join that links to the correct record in Categories.

                   A portal to Category_Join with a drop down equipped _fkCategoryID field is the easiest way to assign records to categories but it's also possible to set up a portal to Categories that looks and functions like a group of check boxes for assigning categories. And the check boxes can be arranged into a horizontal row if necessary.

                   To get the report you want, you can use a list view layout based on Categories and use a calculation field with the Count function to count related records in YourTable to get a count of the number of records in each category.

              • 4. Re: Advice for many counts on one page
                Kj

                     Thank you, this is the help I needed. Thousands of records are imported and this will be done several times. Cannot manually assign each record to a category but I should be able to script it I think. 

                     Not as simple as it first appeared to me (when I did 12 different sub summary reports) :)