Display if null

K

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.

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.
 
J

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.
Click to expand...

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]
 
K

Kaye

It worked! Thanks so much!

John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
K

Kaye

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.

Kaye said:
It worked! Thanks so much!

John Vinson said:
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.
Click to expand...

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]
Click to expand...
Click to expand...
 
K

Kaye

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."

Kaye said:
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.

Kaye said:
It worked! Thanks so much!

John Vinson said:
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]
Click to expand...
Click to expand...
Click to expand...
 
T

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."

Kaye said:
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.

Kaye said:
It worked! Thanks so much!

John Vinson said:
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]
Click to expand...
Click to expand...
Click to expand...
 
K

Kaye

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.

Tom Wickerath said:
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."

Kaye said:
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.

Kaye said:
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]
Click to expand...
Click to expand...
Click to expand...
 
Top