Finding records that do not exist

[Hinfer]

I am using crystal 8.5 and need to find persons where a record does not exist.

I have two tables, person table and a certificates table. I need to find persons where a certificate does not exist for that person. The certificates table creates records when the person has acquired a certificate. I need to find persons where they don't have a specified certificate(s). This would be a parameter for the user to enter which certificate that people don't have.

Person Table
------------
Person ID# Name
123 John Smith
456 Jill Jones
789 Jack Johnson

Certificate Table
-----------------
Person ID# Certificate Code Certificate Number
123 Fire abc
123 Gas def
123 Oil ghi
456 Gas jkl

Certificate Table is linked to Person table by person ID#. I need to find people who HAVE NOT had a FIRE certificate. This would be Jill and Jack. NO RECORED WILL BE CREATED IN THE CERTIFICATE TABLE IF THE PEOPLE DO NOT OBTAIN A CERTIFICATE.

I can do this report in MS access. I create a query (called Query 1) by doing a left outer join between person table and certificate table. In this query, I find people who have FIRE certificate.

Then I create another new query (called Query 2) and do a left outer join between person table and Query 1. I then find people where they have a certificate code that is null.

This works but how would I do this in crystal? I tried to do a subreport and link it to the main but could not to a left outer join link with the main report and I can not do another query on the certificate table to find the nulls (however I could get around this by suppressing the section if the record equals FIRE). How else could I do this?

Essentially I need to find people who have a certificate and then link it back to the person table (left outer join) and then find the nulls.

Any help would be appreciated.

Thanks in advance

 

[hilfy]

-Add both tables to the report.
-Link FROM the {person.Person_ID} field TO the {certificate.Person_ID} field.
-Right-click on the link that you just created. In the bottom right of the screen, select "Left Outer".
-Create a formula (I usually use a formula name of "@PrintIt") that looks like this:
IsNull({certificate.Person_ID})
-Use this new formula in your selection criteria - {@PrintIt} Is True is how the Select Expert should read.

-D

 

[Hinfer]

hilfy,

I have done a left outer join but in doing so, it will only return Jack. It should also return Jill since she doesn't have a FIRE certificate (but has a GAS certificate)

I think I have to do two queries, one to find who has the certificate and another that takes the results of the first query and left join it to the person table to find where the code is null. (I don't know how I would do this in crystal, that is, take a resulting query and then left outer join it back to the person table).