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
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