If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab query to include fields with Null value
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! |
#2
|
|||
|
|||
Crosstab query to include fields with Null value
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. -- Duane Hookom MS Access MVP "Peggy Ball" wrote in message ... 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! |
#3
|
|||
|
|||
Crosstab query to include fields with Null value
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 Duane Hookom wrote: 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. |
Thread Tools | |
Display Modes | |
|
|