6 Replies Latest reply on Feb 11, 2014 11:02 AM by snickwit

    Count Summary field to subtract a number field in related table doesn't calculate correctly

    snickwit

      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:

       

      1. Client (PK_clientID, Client Name, assigned staff initials) with portal to Registered Program
      2. Registered Program (PK_programID, program name, status, type, FK_clientID, Countofprogram)
      3. 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!