Designing a report where matching and unmatching records appear together

[Dave]

I'm in charge of creating reports based on a third-party learning
management system.

I need to create a report that lists the name of a course, and under
it, lists the names of the people who have completed the course
followed by the names of those who haven't completed the course i.e.:

(Filtered by Region)
Excel Beginning
John Doe Completed
Jane Doe Completed
Joe Smith
Betty Jones

I have two tables related to each other:

Employees:
EmpID (Primary Key)
FirstName
LastName
Region (i.e. North, South, East, West)

1 Joe Smith South
2 Betty Jones North
3 John Doe East
4 Jane Doe West

Enrollments:
EnrollmentID (Primary Key)
EmpID (Foreign Key)
CourseTitle
CourseStatus (i.e. Completed, Enrolled, or NULL)
CompletionDate

1 1 Excel Beginning Completed 1/1/05
2 1 Excel Advanced Completed 1/2/05
3 1 Word Beginning Completed 1/3/05
4 2 Excel Beginning Completed 1/4/05
5 2 Excel Advanced Completed 1/5/05
6 2 Word Beginning Completed 1/6/05

A one-to-many relationship exists between the Employees table and
Enrollments table with EmpID as the join field.

If the employee has not completed or is not enrolled in the course, a
record does not exist in the Enrollments table.

I can get the records for the people who completed the course by using
both tables with an inner join, but I'm not sure how to list the other
employees that do not have matching records for a specific course
filtered by a specific region.

Any design ideas would be greatly appreciated.

Thanks,

Dave

 

[Marshall Barton]

I'm in charge of creating reports based on a third-party learning
management system.

I need to create a report that lists the name of a course, and under
it, lists the names of the people who have completed the course
followed by the names of those who haven't completed the course i.e.:

(Filtered by Region)
Excel Beginning
John Doe Completed
Jane Doe Completed
Joe Smith
Betty Jones

I have two tables related to each other:

Employees:
EmpID (Primary Key)
FirstName
LastName
Region (i.e. North, South, East, West)

1 Joe Smith South
2 Betty Jones North
3 John Doe East
4 Jane Doe West

Enrollments:
EnrollmentID (Primary Key)
EmpID (Foreign Key)
CourseTitle
CourseStatus (i.e. Completed, Enrolled, or NULL)
CompletionDate

1 1 Excel Beginning Completed 1/1/05
2 1 Excel Advanced Completed 1/2/05
3 1 Word Beginning Completed 1/3/05
4 2 Excel Beginning Completed 1/4/05
5 2 Excel Advanced Completed 1/5/05
6 2 Word Beginning Completed 1/6/05

A one-to-many relationship exists between the Employees table and
Enrollments table with EmpID as the join field.

If the employee has not completed or is not enrolled in the course, a
record does not exist in the Enrollments table.

I can get the records for the people who completed the course by using
both tables with an inner join, but I'm not sure how to list the other
employees that do not have matching records for a specific course
filtered by a specific region.

Use an outer join instead of the inner join.