Want to find records from one table that are not in another

[Deano1976]

CR v8.5

I want to be able to list employees who have not been on specific training courses.

I have a table of records for each training course an employee has been on called Training_Records. It has the following fields.(One row per course so an employee may have more than one row depending on how many courses they have been on)

Employee Ref, Course Ref

I also have a table of all courses that are available to attend called Courses. There are two fields:

Course Ref, Course Name


I want to be able to have the report show each Employee Ref and the courses from the Course table that they have not attended.

How can I do this?

Do you need more information?

 

[lbass]

Use the employee table only in the main report. Insert a group on Employee Ref. Then insert a subreport and place it in a group header section. Use the course table and employee ref table in the subreport, with a left join FROM the courses table TO the Employee table. Insert a group on course ref. In the subreport linking section, choose {employee.ref} from the main report and allow {?pm-employee ref} in the lower left, but do NOT check "select records based on field". Then in the subreport, create a formula:

//{@incourse}:
if {employee.ref} = {?pm-employee.ref} then 1
Place this in the detail section and then suppress the detail section and all other subreport sections except the group header.

Then, still in the subreport, go to report->selection formula->GROUP->and enter:

sum({@incourse},{course.ref}) = 0

-LB

 

[IdoMillet]

There is another sleek solution (no need for subreports) for this challenge, but please specify what Crystal version you are using and if you are free to create Views in your DBMS.

- Ido


Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com