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".
"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...
Hope someone can help. Thanks