Have Data, Need query

[Beast777]

I have 3 tables. First one holds all employee information with employee ID as key. Second holds all courses with a courseid as key. Third lists all the courses the employees have taken using their employeeid and courseid as reference. All is good to look up who has taken courses such as a report of everyone who has a firstaid course but I need to know everyone who hasnt had the course. Only thing I could think of is to make a query showing all that have and compare to the table of all pers but have no idea of how to go about it. ANy help would be appreciated, thanks.

 

[synapsevampire]

The solution is Crystal version and database dependent.

A Left outer join from Employee to Classes Taken will result in all employees, plus the classes for those employees who have taken a class.

-k

 

[lbass]

First, create the left join from the Employees table to the Courses Taken table on {employee.ID}. Since it sounds like you might want to run the report for different courses, you might want to create a parameter
{?course} using the course ID. Do not use this in the select statement, but instead create a formula {@coursetaken}:

if isnull({coursestaken.courseID}) or
{coursestaken.courseID} <> {?course} then 0 else 1

Next insert a group on {employee.ID} and place {Employee.Name} in the Group Header.

Then go to report->edit selection formula->GROUP and enter:

sum({@coursetaken},{employee.ID}) = 0

This will return only those employees who have not taken the course selected by the parameter.

-LB