Avatar of esbyrt
esbyrt
Flag 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
Microsoft AccessDatabases

Avatar of undefined
Last Comment
esbyrt

8/22/2022 - Mon
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PatHartman

When you are unfamiliar with SQL, it is easiest to use the QBE.  Your query is missing the * in the select clause.
Rey Obrero (Capricorn1)

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

Select FROM tblCertsRequired, Left
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
esbyrt

ASKER
Thanks so much! That did the trick.