Crosstab query does not include records with zero attendance - Access 2003

Question

Hello ~

I have a Access 2003 db to run student attendance reports. The report is based on a crosstab query.  However, I am at a loss as to how to include those students within the same report who have perfect attendance. This report only includes students who have an attendance record.

The report is based on the following two queries:

#1. qryAttendanceData

SELECT tblStudentAttendanceData.Student_Number, tblStudentData.Home_Room, [Last_Name] & ", " & [First_Name] AS Expr1, tblStudentData.First_Name, tblAttendanceCode.AttCode, tblStudentAttendanceData.Att_Date, tblStudentAttendanceData.Att_Comment

FROM (tblStudentData INNER JOIN tblStudentAttendanceData ON tblStudentData.Student_Number=tblStudentAttendanceData.Student_Number) INNER JOIN tblAttendanceCode ON tblStudentAttendanceData.Att_Code=tblAttendanceCode.AttCode

ORDER BY tblStudentData.Home_Room, [Last_Name] & ", " & [First_Name];

#2. qryAttendanceData_Crosstab

TRANSFORM Count(qryAttendanceData.Att_Date) AS CountOfAtt_Date

SELECT qryAttendanceData.Student_Number, qryAttendanceData.Home_Room, qryAttendanceData.Expr1, Count(qryAttendanceData.Att_Date) AS [Total Of Att_Date]

FROM qryAttendanceData

GROUP BY qryAttendanceData.Student_Number, qryAttendanceData.Home_Room, qryAttendanceData.Expr1

PIVOT qryAttendanceData.AttCode;

The above queries return 243 reports but we have 326 studetns (83 with perfect attendance). The report prints in alphabetical order by HomeRoom and I would like to have the perfect attendance reports in the mix.

Thank you,

Peggy

Answer 1

That's because the computed Expr1 column is taken from the qryAttendanceData query, not from the tblStudentData table.  The column from the query will be Null for each 'perfect' student because of the lack of matches in tblStudentAttendanceData.  You can overcome this by changing the references in the cross tab query to 'qryAttendanceData.Expr1' to the expression 'tblStudentData.[Last_Name] & ", " & 'tblStudentData.[First_Name]'.  But the result table will not include any counts for these students as counting Nulls gives you Null.  This may be what you want, but if not I've tried to put forward a solution below which gives you at least some indication that these are students with perfect attendances.

As far as I can see the root of the problem is that those students with perfect attendance are not recorded as such per se, but 'perfection' is implied by the absence of rows in tblStudentAttendanceData.  I have to confess that this sounds somewhat illogical to me, as I'd have expected a perfect attendance record to be represented by rows in tblStudentAttendanceData for all possible attendance dates.  However, this apparently not being the case, probably the best you can do is change the original qryAttendanceData so that it returns all students regardless of matched, by means of LEFT OUTER JOINS, and using the NZ function to substitute values for the Nulls which would otherwise be returned at the attendance data and attendance code column positions where there are not matches.  Try something like the following:

SELECT

    tblStudentData.Student_Number,

    tblStudentData.Home_Room,

    [Last_Name] & ", " & [First_Name] AS FullName,

    tblStudentData.First_Name,

    NZ(tblAttendanceCode.AttCode,"Uncoded") AS AttCode,

    NZ(tblStudentAttendanceData.Att_Date,"All dates") AS Att_Date,

    NZ(tblStudentAttendanceData.Att_Comment,"Perfect attendance") AS Att_Comment

FROM (tblStudentData LEFT JOIN tblStudentAttendanceData

ON tblStudentData.Student_Number=tblStudentAttendanceData.Student_Number)

LEFT JOIN tblAttendanceCode ON tblStudentAttendanceData.Att_Code=tblAttendanceCode.AttCode

ORDER BY tblStudentData.Home_Room, [Last_Name], [First_Name];

You could then base your original crosstab query on this, but note that I've changed the default and rather meaningless Expr1 computed column name to FullName.

While this should ensure that all students are returned the values at the intersections in the crosstab query's result table will be rather inconsistent as for those students with an attendance record the value will be the number of attendances per code recorded, whereas for those with a perfect attendance record the values will, I think, be 1 for the 'Uncoded' column heading, so there are no counts of attendances as such for the 83 students.  But without any data in the base tables to record their attendances there cannot be any counts of course, i.e. the problem stems from the way in which the attendances are recorded.

---

Ken Sheridan, Stafford, England

Answer 2

Hi Karl,

I'm almost there!  Now my qurey has 326 records!  However, the student number and home room fields contain data but the Expr1 (student lastname, firstname) is is blank and the sort order is off.  The sort should be Home_Room, Expr1 (Last_Name, First_Name).  Unfortunately I'm not understading the query string to try to figure this out myself...

Thank you!!!

Peggy

Answer 3

Try this --

TRANSFORM Count(qryAttendanceData.Att_Date) AS CountOfAtt_Date

SELECT tblStudentData.Student_Number, tblStudentData.Home_Room, qryAttendanceData.Expr1, Count(qryAttendanceData.Att_Date) AS [Total Of Att_Date]

FROM tblStudentData LEFT JOIN qryAttendanceData ON tblStudentData.Student_Number = qryAttendanceData.Student_Number

GROUP BY tblStudentData.Student_Number, tblStudentData.Home_Room, qryAttendanceData.Expr1

PIVOT qryAttendanceData.AttCode;

---

Build a little, test a little.

Answer 4

Karl~

I used the text above in my Crosstab query and now I have 244 records but fields in the first record are blank - Student_Number, Home_Room, Expr1 (student name); the Total # of days is 0.

Do I need to add the last three lines to my first query also?

Thank you for your help!

~Peggy

Answer 5

Try this --

TRANSFORM Count(qryAttendanceData.Att_Date) AS CountOfAtt_Date

SELECT qryAttendanceData.Student_Number, qryAttendanceData.Home_Room, qryAttendanceData.Expr1, Count(qryAttendanceData.Att_Date) AS [Total Of Att_Date]

FROM tblStudentData LEFT JOIN qryAttendanceData ON tblStudentData.Student_Number = qryAttendanceData.Student_Number

GROUP BY qryAttendanceData.Student_Number, qryAttendanceData.Home_Room, qryAttendanceData.Expr1

PIVOT qryAttendanceData.AttCode;

---

Build a little, test a little.