Unmatched Query (Kind of)

[lzook88]

In my database I have three tables:



tblUsers
- UserID (AutoNumber)
- FullName
- HashID (User's Login ID)
- Password
- UserSecurity
- EMTLevel
- Station
- COG
- Status
- Locked

tblCertificationTypes
- CertType_ID (Auto Number)
- CertType

tblCertifications
- ID (Auto Number)
- HashID (Lookup from tblUsers FK)
- Certification (Lookup from tblCertifications FK)
- CertNum
- Issued
- Expiration

What I am trying to do is create a query that tells me who does not have certain certifications. The query would look at tblCertifications and output all the tblCertificationTypes that a user in tblUsers does not have.

I have tried looking around online and messing with the query myself. All I end up getting is everyone with every certification type whether they are certified or not.

I appreciate any help you all can offer me on this subject.

Thanks,
Z

[ranman256]

Make a query Q1 using tblCerts & tblUsers, but DO NOT JOIN THEM.
this makes a list of ALL users with ALL certs. (All possible)

Now Q2, join tblUsers to tblCerts , (all that exist)

then to get what's missing, Q3= Q1 and Q2 joined on user and Cert, but use OUTER JOIN.
show ALL items in Q2, and what is missing in Q1.
this shows what a user does not have.

[lzook88]

Okay.....

I have Q1 to show all possible certifications a person could have
I have Q2 to show all certifications a person currently holds

But I can not figure out Q3 to get it to show what someone doesn't have.

Thanks
Z

[lzook88]

See attached pictures:

1.) All Possible Matches




2.) All Existing Certifications




Thanks Again!

[lzook88]

Bump...

[lzook88]

Was able to solve the issue with this.. Will post the final fix when I have a moment later today!

Thanks for your help as always AccesForums.Net!
Z