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!
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
I have attached my database in progress. It isn't pretty yet but the basic function should be clear. Thanks!
Test1.accdb
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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()
Select FROM tblCertsRequired, Left Join tblCertHeld on tblCertsRequired.CertID = tblCertHeld.CertIDNum
Where tblCertHeld.CertIDNum Is Null or tblCertHeld.ExpiryDate <= Date()
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()
Select * FROM tblCertsRequired
Left Join tblCertHeld on tblCertsRequired.CertID = tblCertHeld.CertIDNum
Where tblCertHeld.CertIDNum Is Null or tblCertHeld.ExpiryDate <= Date()
Premium Content
You need an Expert Office subscription to comment.Start Free Trial