Query calculation from a form???

[daisy19]

Hi guys, so here goes... pretty clueless at access..



On a database I didn't create and can't contact the developer, I have a counselling subform for patients with a drop down menu of which counsellor saw them. There is a space for entry in an attendance box for the letter D if they failed to attend. This is labelled subform in the attachment.

The table (1a) that holds the subform data is also on the attachment.

Along with this there is a crosstab query which calculates on a monthly basis how many patients have not attended i.e. got D in their attendance box. sql code for this is...

TRANSFORM Count([1a].AppointmentNo) AS CountOfAppointmentNo
SELECT [1a].Month
FROM 1a
GROUP BY [1a].Month
PIVOT [1a].Attendance;

and the crosstab query is in the attachments (ignore the 5 and 7619 fields)

I want something similar to this but what I want to know is is it possible for me to create a query (or a table), which shows the total number of patients who haven't attended (got D in the attendance box) for each individual counsellor and ideally to have it from the start of this month.

A tricky bit may be that there have been no entries on the subform and hence table yet for the counsellor field so this might need to be set up in line for future usage??

Any thing else that you need to know, just ask. Thanks!!!! daisy x

[TheShabz]

Will this span multiple months? I ask because your Month field doesnt look like a date/time field. Without it, it will be hard to do it. I'll go with what you have, assuming Month is a text field and you only need Jul10

SELECT Counsellor, Count([Client Number])
FROM 1a
WHERE Attendance = "D"
AND Month = "Jul10"
GROUP BY Counsellor

Oh and FYI, Firefox says you spelled "counsellor" incorrectly.