1 Reply Latest reply on Nov 20, 2014 8:57 AM by erolst

    Making a Report with multiple totals from a single field's data

    simonpsmith
      I'm quite new to FileMaker and hope someone can help lead me in the right direction with methods of reporting something that I cannot get my head around. I've looked at many examples of 'counting' and Summaries but none seem to answer what I need to do.

       

      To see if I could get it to work I started with a basic database at this stage that contains just 3 tables: "Counsellors", "Clients" and "Sessions".

       

      At present:

      "Counsellors" has 2 fields: CounsellorID and CounsellorName

      "Clients" has 3 fields: ClientID, ClientName and CounsellorName (their allotted counsellor)

      "Sessions" has 4 fields: SessionID, SessionDate, ClientID and SessionStatus

       

      SessionStatus can be "Attended", "Missed" or "Cancelled".

       

      The first report I've been trying to make work is a list, by client, of how many sessions there have been in their name in total, and a breakdown of how many they attended, how many they missed, and how many they cancelled.

       

      Following on from that, I want to be able to be able to report by counsellor, so that under each counsellor's name I have a list of each of their allotted clients, their individual attended/missed/cancelled details, and then a grand total of attended/missed/cancelled/total sessions for all of their allotted clients (ideally with the capability of selecting a range of dates to report between).

       

      I can get a count of total sessions per client by using "= Count ( Sessions:ClientID )", but cannot fathom how to get, for instance, the count, by client, of a single type of field entry, such as number of only "Missed" sessions.

       

      So, basically, what I want to get out is something like...

       

      ClientIDAttendedCancelledMissedTotal
      101105116
      10296015
      1035139

      and then...

      CounsellorAClientIDAttendedCancelledMissedTotal
      101105116
      10296015
      1911131
      CounsellorBClientIDAttendedCancelledMissedTotal
      1035139

       

      Hope someone can help. Thanks