AnsweredAssumed Answered

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

Question asked by simonpsmith on Nov 20, 2014
Latest reply on Nov 20, 2014 by erolst
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

Outcomes