I’m unable to use a Count Summary field in one table to subtract an amount stored in field in another related table:
I have 3 related tables:
- Client (PK_clientID, Client Name, assigned staff initials) with portal to Registered Program
- Registered Program (PK_programID, program name, status, type, FK_clientID, Countofprogram)
- Assigned Staff (initials, Staff name, MAX (which is maximum programs allowed)
The count summary field CountofPrograms in Registered Program table I primarily use for summary parts in reports to display Count of different program types and status.
I can calculate the number of programs assigned or enrolled to an assigned staff member which I can find by building summary report displaying from Registered Program data and sorting by Assigned Staff & program type using CountOfPrograms field. However, I need to also subtract the total program type assigned to staff from their max based on searching only programs that have “active” status it only works when I query one staff name at a time on the report. When I build the report listing each staff name, the count is incorrect. I’ve tried calculation field in Assigned Staff table using (MAX – RegisteredProgram:CountofPrograms) and that fails. I also tried calculation field in the Registered Program table using (CountofPrograms-AssignedStaff:Max) and same issues. The numbers are large and appear to be counting all programs assigned to staff but I’m filtering out my search to find only those with “active” RegisteredProgram: Status = active.
I've tried to search for solutions but I'm a bit at loss at to what solution I need to implement. If there's more information needed, let me know - thanks much!