Crosstab query to include fields with Null value

[Peggy Ball]

Hello ~
I have created a report based on a crosstab query for student attendance.

Row = Name
Columns = DR, Ed, FE, ILL, RH, TEX, TRP, TUN, UNV, UNX (absent reasons.
Each column show the number of dates (count) that child has been absent for
that particular reason)
Total = counts the total number of absent dates.

This works almost great if I'm printing the reports for the entire school
because there is at least 1 absence in every column. It will not print a
report for a student if he/she hasn't been absent.

I need to be able to filter the report by either class or student name.
I've created a parameter query then based the crosstab on that but when
running the report not all of the students in the class have a report if
someone in the class hasn't been absent for one or more of the excuses
listed.

How can I include a report for a student if they have never been absent?
The report would only list the columns with no numbers.

And, how can I filter the report by class or last name?

Thank you for the help!

 

[Duane Hookom]

First, you might want to set the Column Headings property to all possible
absent reasons.
Column Headings: "DR","Ed","FE","ELL",.....

Then join your crosstab to a table or query with all students. Use a LEFT or
RIGHT JOIN to include all the students from the one table/query.

 

[John Spencer]

Peggy,

For future reference, it helps if you post the SQL of the query (or queries)
involved. Sometimes we can modify the specific query for you and almost
always we can better understand the problem.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County