I have a simple db with three tables: Clients, Janitors, and Accounts. Accounts is a join table. I have a problem in a report. The report is based in the child table (Accounts). I want to organize the report by a calculation field in the Janitors table, so the first break field is from the Janitor table. It counts the status of the Janitor's Insurance standing (Current, Expired, or Upcoming).

Underneath that heading I have the Janitor in another sub-summary part and then their clients in the body part.

I want to add a field on the first sub-summary line (Current, Expired, and Upcoming) that contains the number of Janitors in the next sub-summary section. However, nothing I've tried works. Summary fields, for instance, count the number of records in the child table; so, in the example attached, there are 8 janitor companies, but one has two clients. I want that section to indicate "8" rather than "9", but I can't figure out how.

I've tried many combinations of summary and calculation fields, but no luck. You can see my attempts in the file, under Define Database/Fields in the "--- F A I L ---" section.

Any insights?

Example_copy.fp7.zip

This is all done from the same table that the report is based upon. However, the solution requires using the GetSummary() function - and GetSummary() cannot use a related field as the breakField. Therefore you need to change your sub-summary part by Janitor to use a "local" field instead (and make sure you sort by it, too).

Example2.fp7.zip

Thanks comment. I will have to study this a little more in-depth. I'm not very familiar with the GetSummary() function and had not considered that the solution might require several fields.

Thanks.

dan