Find data that does not match

[WelderBill]

What I need to do is see what test number an employee does not have. I have
three tables one with just employees, linked to a table with all the tests
he took; I also have a table with all the tests that are required to be taken.
How do I see what he has not taken?

Thanks,

 

[MGFoster]

What I need to do is see what test number an employee does not have. I have
three tables one with just employees, linked to a table with all the tests
he took; I also have a table with all the tests that are required to be taken.
How do I see what he has not taken?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this, perhaps:

SELECT E.employee_id, T.test_id
FROM Tests AS T LEFT JOIN (Employees As E INNER JOIN EmployeeTests As ET
ON E.employee_id = ET.Employee_id) ON T.test_id = ET.test_id
WHERE ET.test_id IS NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdUK5YechKqOuFEgEQIFmwCg6L84vfGYrIrKN+FRt9b0Ne2HkngAoPTh
olte97pOkdPK/WReqkwpsRei
=fT3X
-----END PGP SIGNATURE-----

 

[WelderBill]

Something like this, perhaps:

SELECT E.employee_id, T.test_id
FROM Tests AS T LEFT JOIN (Employees As E INNER JOIN EmployeeTests As ET
ON E.employee_id = ET.Employee_id) ON T.test_id = ET.test_id
WHERE ET.test_id IS NULL

Thanks for the help, put I know just enough about Access to be dangerous. If
I supplied more information it might help me.

The table with all the employees is named EmployeeTbl and has a related
field named EmployeeNum in the table named WelderQuaTbl. The table named
WelderQuaTbl has a field named SST which has all the tests taken (like he
took test 1,2,4,6) I also have a Table named TestTbl which has all the test
numbers possible to take (like 1,2,3,4,5,6.3,6.4) this field is named SST.
What I what to know how to look at the TestTbl and see what tests have not
been taken. Also is this the best way to retrieve this information?

Thanks,
In advance

 

[MGFoster]

Thanks for the help, put I know just enough about Access to be dangerous. If
I supplied more information it might help me.

The table with all the employees is named EmployeeTbl and has a related
field named EmployeeNum in the table named WelderQuaTbl. The table named
WelderQuaTbl has a field named SST which has all the tests taken (like he
took test 1,2,4,6) I also have a Table named TestTbl which has all the test
numbers possible to take (like 1,2,3,4,5,6.3,6.4) this field is named SST.
What I what to know how to look at the TestTbl and see what tests have not
been taken. Also is this the best way to retrieve this information?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Queries are the usual way to retrieve info in a relational database.
Just copy & paste the SQL into the SQL View of a Query. You can then
switch over to the Design Grid View if you want to see how it's done on
that side. All you have to do is substitute your table/column names in
my SQL command. I realized that the original solution I posted had an
incorrect table JOIN, so I re-did it with a derived table and formated
the SQL for clarity.

ISO standards recommend that table names not have the "tbl" designator,
but I'll use your table names, anyway.

SELECT ET.EmployeeNum,
T.SST
FROM TestTbl AS T
LEFT JOIN
(SELECT E.EmployeeNum,
W.SST
FROM EmployeeTbl AS E
INNER JOIN WelderQuaTbl AS W
ON E.EmployeeNum = W.EmployeeNum
) As ET
ON T.SST = ET.SST
WHERE ET.SST Is Null

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdWKYYechKqOuFEgEQKWswCfRwREk8FhIePKZikiCPRWsPa8XXsAnj//
rWG/QFcFnE2xlHVsLD0BhuSh
=QmYa
-----END PGP SIGNATURE-----

 

[WelderBill via AccessMonster.com]

[quoted text clipped - 17 lines]

What I what to know how to look at the TestTbl and see what tests have not
been taken. Also is this the best way to retrieve this information?

Queries are the usual way to retrieve info in a relational database.
Just copy & paste the SQL into the SQL View of a Query. You can then
switch over to the Design Grid View if you want to see how it's done on
that side. All you have to do is substitute your table/column names in
my SQL command. I realized that the original solution I posted had an
incorrect table JOIN, so I re-did it with a derived table and formated
the SQL for clarity.

ISO standards recommend that table names not have the "tbl" designator,
but I'll use your table names, anyway.

SELECT ET.EmployeeNum,
T.SST
FROM TestTbl AS T
LEFT JOIN
(SELECT E.EmployeeNum,
W.SST
FROM EmployeeTbl AS E
INNER JOIN WelderQuaTbl AS W
ON E.EmployeeNum = W.EmployeeNum
) As ET
ON T.SST = ET.SST
WHERE ET.SST Is Null

HTH,

MGFoster
Thanks for all the help.
Welder Bill