calculate present and absent

[sahh1419]

i have two tables. table-1 contains 3 fields and table 2 contains two fields. i want to get the result in group by class on specific date. please view the attached file.

 

[isladogs]

Its not clear from the screenshot how the data indicates who is present or absent

 

[arnelgp]

try this Query:

PARAMETERS [Enter Date] DateTime;
SELECT DISTINCT T1.Class, (SELECT COUNT(*) FROM [TABLE-1] WHERE [TABLE-1].CLASS=T1.CLASS) AS [Total Strength], (SELECT COUNT(*) FROM [table-2] WHERE USERID IN (SELECT USERID FROM [table-1] WHERE [TABLE-1].Class=T1.Class ) AND [TABLE-2].CheckTime=[Enter Date]) AS Present,(SELECT COUNT(*) FROM [TABLE-1] WHERE [TABLE-1].CLASS=T1.CLASS)-(SELECT COUNT(*) FROM [table-2] WHERE USERID IN (SELECT USERID FROM [table-1] WHERE [TABLE-1].Class=T1.Class ) AND [TABLE-2].CheckTime=[Enter Date]) As Absent
FROM [Table-1] AS T1