Display if null

[Guest]

Using Access as a reportwriter for an education system, I have a query of
selected employees, a query of selected courses, and a query of the selected
courses that the selected employees passed. I want to print a report of all
employees with all courses, whether they passed or did not attempt.

EXAMPLE:

QUERY 1
Employee 1
Employee 2

QUERY 2
Course 1
Course 2

QUERY 3
Employee 1
Course 1
Passed

I want the report to show:

Employee 1 Course 1 Passed
Employee 1 Course 2 Not attempted

Employee 2 Course 1 Not attempted
Employee 2 Course 2 Not attempted

Any suggestions would be greatly appreciated.

 

[John Vinson]

Using Access as a reportwriter for an education system, I have a query of
selected employees, a query of selected courses, and a query of the selected
courses that the selected employees passed. I want to print a report of all
employees with all courses, whether they passed or did not attempt.

Use an "Outer Join". Join your (oddly named) QUERY 1 to QUERY 3 by the
employeeID; select the Join line and choose Option 2 - "Show all
records in QUERY 1 and matching records in QUERY 3".

Then change this query to a Crosstab query using the employee ID as
the row header and the course as the column header. You can use a
calculated field, NZ([Course ID], "Not Attempted") to display text for
the NULL values that the outer join will give you.

John W. Vinson[MVP]

 

[Guest]

It worked! Thanks so much!

Using Access as a reportwriter for an education system, I have a query of
selected employees, a query of selected courses, and a query of the selected
courses that the selected employees passed. I want to print a report of all
employees with all courses, whether they passed or did not attempt.

Use an "Outer Join". Join your (oddly named) QUERY 1 to QUERY 3 by the
employeeID; select the Join line and choose Option 2 - "Show all
records in QUERY 1 and matching records in QUERY 3".

Then change this query to a Crosstab query using the employee ID as
the row header and the course as the column header. You can use a
calculated field, NZ([Course ID], "Not Attempted") to display text for
the NULL values that the outer join will give you.

John W. Vinson[MVP]

 

[Guest]

Except....the queries that I'm using to select employees and courses are
based on criteria specified in a form. When I try to run the "final"
crosstab query, it gives me an error that the data
[forms]![frmCoursesAvailable]![Beg Date] cannot be validated. If I hard code
the query it works.

It worked! Thanks so much!

Using Access as a reportwriter for an education system, I have a query of
selected employees, a query of selected courses, and a query of the selected
courses that the selected employees passed. I want to print a report of all
employees with all courses, whether they passed or did not attempt.

Use an "Outer Join". Join your (oddly named) QUERY 1 to QUERY 3 by the
employeeID; select the Join line and choose Option 2 - "Show all
records in QUERY 1 and matching records in QUERY 3".

Then change this query to a Crosstab query using the employee ID as
the row header and the course as the column header. You can use a
calculated field, NZ([Course ID], "Not Attempted") to display text for
the NULL values that the outer join will give you.

John W. Vinson[MVP]

 

[Guest]

Correction to my previous post: Error is "The Microsoft Jet Database ending
does not recognize '[forms]![frmCoursesAvailable]![Beg Date]' as a valid
field name or expression."

Except....the queries that I'm using to select employees and courses are
based on criteria specified in a form. When I try to run the "final"
crosstab query, it gives me an error that the data
[forms]![frmCoursesAvailable]![Beg Date] cannot be validated. If I hard code
the query it works.

It worked! Thanks so much!

On Thu, 23 Dec 2004 13:19:03 -0800, "Kaye"

Using Access as a reportwriter for an education system, I have a query of
selected employees, a query of selected courses, and a query of the selected
courses that the selected employees passed. I want to print a report of all
employees with all courses, whether they passed or did not attempt.

Use an "Outer Join". Join your (oddly named) QUERY 1 to QUERY 3 by the
employeeID; select the Join line and choose Option 2 - "Show all
records in QUERY 1 and matching records in QUERY 3".

Then change this query to a Crosstab query using the employee ID as
the row header and the course as the column header. You can use a
calculated field, NZ([Course ID], "Not Attempted") to display text for
the NULL values that the outer join will give you.

John W. Vinson[MVP]

 

[Tom Wickerath]

Hi Kaye,

Please see the following KB article:

Error When Running Crosstab Query with a Parameter
http://support.microsoft.com/?id=209778

You might also be interested in seeing my crosstab query tutorial, available at:
http://www.access.qbuilt.com/html/crosstab_queries.html

This error is mentioned in the tutorial, just prior to the Summary section.


Tom
________________________________


Correction to my previous post: Error is "The Microsoft Jet Database ending
does not recognize '[forms]![frmCoursesAvailable]![Beg Date]' as a valid
field name or expression."

Except....the queries that I'm using to select employees and courses are
based on criteria specified in a form. When I try to run the "final"
crosstab query, it gives me an error that the data
[forms]![frmCoursesAvailable]![Beg Date] cannot be validated. If I hard code
the query it works.

It worked! Thanks so much!

On Thu, 23 Dec 2004 13:19:03 -0800, "Kaye"

Using Access as a reportwriter for an education system, I have a query of
selected employees, a query of selected courses, and a query of the selected
courses that the selected employees passed. I want to print a report of all
employees with all courses, whether they passed or did not attempt.

Use an "Outer Join". Join your (oddly named) QUERY 1 to QUERY 3 by the
employeeID; select the Join line and choose Option 2 - "Show all
records in QUERY 1 and matching records in QUERY 3".

Then change this query to a Crosstab query using the employee ID as
the row header and the course as the column header. You can use a
calculated field, NZ([Course ID], "Not Attempted") to display text for
the NULL values that the outer join will give you.

John W. Vinson[MVP]

 

[Guest]

Thanks for the information. I had to use a workaround to generate the
reports (hard coding the column headers), so now I have a little more time to
do it right. I'll reply when I try it.

Hi Kaye,

Please see the following KB article:

Error When Running Crosstab Query with a Parameter
http://support.microsoft.com/?id=209778

You might also be interested in seeing my crosstab query tutorial, available at:
http://www.access.qbuilt.com/html/crosstab_queries.html

This error is mentioned in the tutorial, just prior to the Summary section.


Tom
________________________________


Correction to my previous post: Error is "The Microsoft Jet Database ending
does not recognize '[forms]![frmCoursesAvailable]![Beg Date]' as a valid
field name or expression."

Except....the queries that I'm using to select employees and courses are
based on criteria specified in a form. When I try to run the "final"
crosstab query, it gives me an error that the data
[forms]![frmCoursesAvailable]![Beg Date] cannot be validated. If I hard code
the query it works.

It worked! Thanks so much!

:

On Thu, 23 Dec 2004 13:19:03 -0800, "Kaye"

Using Access as a reportwriter for an education system, I have a query of
selected employees, a query of selected courses, and a query of the selected
courses that the selected employees passed. I want to print a report of all
employees with all courses, whether they passed or did not attempt.

Use an "Outer Join". Join your (oddly named) QUERY 1 to QUERY 3 by the
employeeID; select the Join line and choose Option 2 - "Show all
records in QUERY 1 and matching records in QUERY 3".

Then change this query to a Crosstab query using the employee ID as
the row header and the course as the column header. You can use a
calculated field, NZ([Course ID], "Not Attempted") to display text for
the NULL values that the outer join will give you.

John W. Vinson[MVP]