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?
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.
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::__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.
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) :)