Finding records that DO NOT exist 1

[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

 

[GJParker]

Link your tables with left outer.

In your selection formula use

(IsNull([MyTable.CertificateCode}) Or {MyTable.CertificateCode} = {?CertificateCode}

This should return a list of all people with and without the prompted certificate code.

You could then Group by Certificate code and suppress any records that have a certificate Code, or enter a group selection formula to filter out the people with certificates.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[Hinfer]

GJParker,

I have done a left outer join but in doing so, it will return John and Jill. It should not return John since John has a FIRE certificate. It is returning John because he has two other certificates that does not equal FIRE.

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.

 

[lbass]

One approach is to use a left join from the Persons table to the Certificates table, and use no selection criteria. Group on {Person.PersonID} and then create a formula {@cert}:

if isnull({cert.certID}) or
{cert.certID} <> {?Certificate} then 0 else 1

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

sum({@cert},{Person.PersonID}) = 0

This will return only those people without the {?Certificate}.

-LB

 

[Hinfer]

A star for you! That is one easy and simple solution!

Thanks alot