How to compare two tables in access and filter by two criteria

esbyrt
esbyrt used Ask the Experts™
on
HI. I am building an employee database. I have two subforms on the employee form in a tab control. There is a Certification Required subform and a Certifications Held subform. I need to compare the certs required to the certs held and return a list of missing or expired certifications. I can put that in another subform/tab or as a report. I need to filter the results by employee ID and also by Position ID. Certs required varies by Position ID. Can this be done in a query?
I have attached my database in progress. It isn't pretty yet but the basic function should be clear. Thanks!
Test1.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
Create a query that uses a left join.
Select ...
From tblRequired, Left Join tblHeld on tblRequired.CertID = tblHeld.CertID
Where tblHeld.CertID Is Null or tblHeld.ExpireDate <= Date()

This will select "missing" certs or expired certs.  I can't download the db right now so hopefully this explanation will suffice.

Author

Commented:
This is what I put in query (updated for my actual field names) but I keep getting an error that a reserved word is used, missing or misspelled argument, or incorrect punctuation. Can you spot the error? Thanks!

Select FROM tblCertsRequired, Left Join tblCertHeld on tblCertsRequired.CertID = tblCertHeld.CertIDNum
Where tblCertHeld.CertIDNum Is Null or tblCertHeld.ExpiryDate <= Date()
Top Expert 2016
Commented:
try this, copy and paste

Select * FROM tblCertsRequired
 Left Join tblCertHeld on tblCertsRequired.CertID = tblCertHeld.CertIDNum
 Where tblCertHeld.CertIDNum Is Null or tblCertHeld.ExpiryDate <= Date()
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
When you are unfamiliar with SQL, it is easiest to use the QBE.  Your query is missing the * in the select clause.
Top Expert 2016

Commented:
it also have an unnecessary comma before the word "Left"

Select FROM tblCertsRequired, Left

Author

Commented:
Thanks so much! That did the trick.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial