multiple counts one record

[dave796]

im trying to get 2 counts onto one record in MS access I currently have 2 queries to generate the 2 counts and then join them but with to combine this all into one. The first query gets a count like this:

SELECT Count(sttdstud.student_id) AS [Actual Attendances], Mid$([SchoolDept],1,1) AS School
FROM (sttrgaos INNER JOIN sttdstud ON (sttrgaos.acad_period = sttdstud.acad_period) AND (sttrgaos.register_id = sttdstud.register_id) AND (sttrgaos.register_group = sttdstud.register_group)) INNER JOIN [School Groups] ON (sttdstud.register_group = [School Groups].register_group) AND (sttdstud.register_id = [School Groups].register_id) AND (sttdstud.acad_period = [School Groups].acad_period)
WHERE (((sttrgaos.acad_period)="0607") AND ((sttdstud.attendance_type)="PR"))
GROUP BY Mid$([SchoolDept],1,1);

this will output 2 column names Actual Attendance which is the count and School which is the unique school identifier. I then want another field with possible attendance in. This is the same query but attendance_type can equal PR AA or AB. How can I join these into one query

 

[r937]

SELECT SUM(IIF(sttdstud.attendance_type='PR',1,0)) AS [Actual Attendances], Count(sttdstud.student_id) AS [Possible Attendances], Mid$([SchoolDept],1,1) AS School FROM (sttrgaos INNER JOIN sttdstud ON (sttrgaos.acad_period = sttdstud.acad_period) AND (sttrgaos.register_id = sttdstud.register_id) AND (sttrgaos.register_group = sttdstud.register_group)) INNER JOIN [School Groups] ON (sttdstud.register_group = [School Groups].register_group) AND (sttdstud.register_id = [School Groups].register_id) AND (sttdstud.acad_period = [School Groups].acad_period) WHERE (((sttrgaos.acad_period)="0607") AND ((sttdstud.attendance_type)IN ('PR','AA','AB'))) GROUP BY Mid$([SchoolDept],1,1);

r937.com | rudy.ca

 

[dave796]

Thanks thought there would be an easier way than what I was thinking of.