Show all from Table A when not in Table B

[pw090466]

Hope someone can help me with this. I use Crystal 10 on an SQL database. I have 2 tables - one shows all staff and the other shows a record for each course staff have attended. Each table has Employee_Number as a link.

I need to show a list of all staff that have NOT been on a particular course with the course name as a parameter field.

Have tried various join types and all I can get is either a list of all staff that have not been on any course or no results at all.

Can anyone help me please??

Thanks.

 

[dgillz]

Use a left outer join, from Table A to Table B, and when you place the Employee_Number on the report, make sure you use the field from Table A, where all employees are listed.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[pw090466]

Yes I tried just that but all that does is give me a list of employees who have attended the parameter selected course.

I did try adding in the criteria of:

{Table B.Course Name} = {@Parameter} and
Isnull({Table B.Employee_Number})

But this didn't work either as it returned no results.

 

[dgillz]

Put the Isnull() at the beginning of the record selction formula. Then let me know if you are not getting the desired results.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

Use a left join from the employee table to the course table. Do not put the parameter in the record selection formula. Instead, first group on {employee.employee} and then create a formula {@tookcourse}:

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

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

sum{@tookcourse},{employee.employee}) = 0

-LB