Link to home
Start Free TrialLog in
Avatar of esbyrt
esbyrtFlag for Canada

asked on

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

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
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of esbyrt

ASKER

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()
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
When you are unfamiliar with SQL, it is easiest to use the QBE.  Your query is missing the * in the select clause.
it also have an unnecessary comma before the word "Left"

Select FROM tblCertsRequired, Left
Avatar of esbyrt

ASKER

Thanks so much! That did the trick.