need present and absent student.

[shah1419]

Dear All,


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.

[ranman256]

2 queries:
present is easy because they attended.

but absent must be an OUTER join.
join the class Roster table to the class attend date table
dbl-click the join line,
select ALL records in ClassRoster, some records in classAttendDate
bring down fields: tClassRoster.[id] , and tClassAttendDate.[id]
under criteria for tClassAttendDate.[id] , set: =null

this says who was NOT there on that date.

[June7]

BTW, Name is a reserved word and should not use reserved words as names for anything. Also, advise no spaces or punctuation/special characters (underscore only exception).

Userid in Table2 is a FK, not PK.

Date value includes the time and I see the same date with different times. Need to extract the date part. Regarding use of international date, review http://allenbrowne.com/ser-36.html.

@ranman, the date field will be Null so there will be no way to know what that absence date is which prevents relating, grouping, and filtering by date. Need a dataset of all possible user and date pairs. This requires a Cartesian relation query of the two tables.

@shah, your Present and Absent values appear to be in wrong columns.

Query1: AllUserDate
SELECT DISTINCT Table1.Userid, Int([Checktime]) AS Dt, Table1.Class FROM Table1, Table2;

Query2: Table2Adj
SELECT Table2.Userid, Int([Checktime]) AS Dt, Table1.Class
FROM Table1 INNER JOIN Table2 ON Table1.Userid = Table2.Userid;

Query3:
SELECT AllUserDate.Dt, AllUserDate.Class, Count(AllUserDate.Userid) AS Total, Sum(IIf(Not [Table2Adj].[Dt] Is Null,1,0)) AS Present, Sum(IIf([Table2Adj].[Dt] Is Null,1,0)) AS Absent
FROM Table2Adj RIGHT JOIN AllUserDate ON (Table2Adj.Dt = AllUserDate.Dt) AND (Table2Adj.Userid = AllUserDate.Userid)
GROUP BY AllUserDate.Dt, AllUserDate.Class;

[ssanfu]

"Class" is also a reserved word; should not use reserved words as names for anything.