Comparing two fields in two different tables using another field value

[rjhartman]

Hi

Can someone help me solve my access woes.

I want to query the values in a table with the values in the field of another table but... i need to loop through the records in the second table using another field. For example, KEY_CERTIFICATES (table1) contains a field called CertificateID. EMPLOYEE_CERTS (table2) contains a field called CertificateID and another called EmployeeID. I simply want to find out those employees that haven't got all (or any) of the key certificates.

Perhaps this might help

KEY_CERTIFICATES (table1)

CertID

1
2
3

EMPLOYEE_CERTS (table2)

EmployeeID CertID

1 ............. 1
1 ............. 2
1 ............. 3
4 ............. 1
4 ..............7
4 ..............8
5 ..............9
5 .............10
5 .............18

As you can see, the second and third EmployeeID (4,5) would flag up because they have not got all the key certificates or haven't got any.

Would appreciate some wisdom from a programmer. I have tried an unmatched query and tried also using a For loop with an IF statement inside. The precise code alludes me.

Kind regards

Richard

 

[Cowboy]

Hi rjhartman.

I think your post might be better suited for a thread in the Queries section of the forums.

*Note: I'm still learning, so this might not work*

I would try running the following query:

"SELECT EMPLOYEE_CERTS.EmloyeeID WHERE EMPLOYEE_CERTS.CertID <> KEY_CERTIFICATES.CertID"

Hope that helps.

Perhaps a Moderator can move this thread for you.

 

[rjhartman]

Thanks Cowboy but that's an unmatched query which i have already tried. I will try your suggestion.

Richard

 

[rjhartman]

querying two fields in two tables using a conditional statement?

Hi

Can someone help me solve my access woes.

I want to query the values in a table with the values in the field of another table but... i need to loop through the records in the second table using another field. For example, KEY_CERTIFICATES (table1) contains a field called CertificateID. EMPLOYEE_CERTS (table2) contains a field called CertificateID and another called EmployeeID. I simply want to find out those employees that haven't got all (or any) of the key certificates.

Perhaps this might help

KEY_CERTIFICATES (table1)

CertID

1
2
3

EMPLOYEE_CERTS (table2)

EmployeeID CertID

1 ............. 1
1 ............. 2
1 ............. 3
4 ............. 1
4 ..............7
4 ..............8
5 ..............9
5 .............10
5 .............18

As you can see, the second and third EmployeeID (4,5) would flag up because they have not got all the key certificates or haven't got any.

Would appreciate some wisdom from a programmer. I have tried an unmatched query and tried also using a For loop with an IF statement inside. The precise code alludes me.

Kind regards

Richard

 

[namliam]

Re: querying two fields in two tables using a conditional statement?

How about you do a outerjoin with some trickery, 2 step process
1) Make a query add both tables and not join them at all (cartesian product)
Something like so:
SELECT EmployeeID , Cert.CertID
FROM Emp, Cert
GROUP BY EmployeeID , Cert.CertID;

This sets up all employees with the key certids.

2)
Make a query to make a unmatched between emp and above query

Done...

 

[Cowboy]

Re: querying two fields in two tables using a conditional statement?

Here is an SQL statement that should work - just tweak the table names/fields to match yours:

SELECT DISTINCT tblEmployeeCerts.EmployeeID
FROM tblEmployeeCerts
LEFT JOIN tblKeyCerts ON tblEmployeeCerts.fkCertID = tblKeyCerts.pkCertID
WHERE tblKeyCerts.pkCertID IS NULL
UNION
SELECT tblKeyCerts.pkCertID
FROM tblKeyCerts
LEFT JOIN tblEmployeeCerts ON tblKeyCerts.pkCertID = tblEmployeeCerts.fkCertID
WHERE tblEmployeeCerts.fkCertID IS NULL;

Basically just creates the union of two left joins.

Using your data but my field/table names, I got: 4 & 5 as the results.

Here is the reference that helped me figure it out:
http://bytes.com/topic/access/answers/759495-finding-not-equal-data-sets

 

[rjhartman]

Thanks Cowboy,

That worked. it has flagged up the correct employees. So thanks for helping out!!

 

[Cowboy]

No problem.

Glad I could help out!

 

[rjhartman]

I now want to try and extend this query. I want to find out which key certificates each employee doesn't have. I think this is far more difficult. I have tried a few things out. The only thing I can think of is to write a function in vba which compares each employee's certificates with the key_certs table and store the result in an array which will then be output to a query.

Don't worry if you don't have time, i'll work it out eventually.

Cheers

Richard