Selecting different values from fields in different rows? 1

[cdgeer]

I have a table with the following columns. Supervisors, EEID, Screen Number. EEID is a unique identifier for each employee that reports to one of the supervisors. I need to create a query that selects all of the employees that report to each supervisor. Then, each of those employees has many screen numbers. I need to select the screen numbers that each employee has that the other employees who report to the same supervisor don't have.(I need to find the differences among them.) I can't figure out how to form a query like this. I bought a 1500 page book on Access 2003 and can't find anything in it that helps! Can anyone PLEASE help?

 

[BigRed1212]

Put up some data and the desired result. That will probably increase your chances of a good response.

 

[MajP]

It may help if you use faq700-6905 to show us your tables. Then provide some real data, and desired output as already stated. My first guess is that your tables are not normalized. I would think

tblEmployees
EEID (pk)
supID_fk (an employee reports to only one supervisor)
other employee type fields

tblSupervisors
supID (pk)
and other supervisor fields

tblScreens
screenNumber (pk)
other screen fields

juncTblEmpScreen
empID_fk
screenNumber_fk (if screens do not have more information than just a number you can delete tblScreens)

Because according to what you said: A supervisor has many employees, and an employee has one supervisor, an employee has many screens.

But using your scheme maybe like.

EEID Supervisor screenNumber
Smith White 1
Smith White 2
Jones White 1
Jones White 3
Jones White 4
Brown Black 1

smith and jones work for white (and they both have screen one, but smith has 2, and Jones has 3,4)
Brown has screen 1 but does not work for white.

So the final unique screens per supervisor is

EEID Supervisor screenNumber
Brown Black 1
Jones White 3
Jones White 4
Smith White 2

I am not very good at SQL so I do it in parts.
1)find what is in common
qryCommon

SELECT A.EEID, B.EEID, A.Supervisor, A.screenNumber
FROM tblEmpScreens AS A INNER JOIN tblEmpScreens AS B ON (A.screenNumber = B.screenNumber) AND (A.Supervisor = B.Supervisor)
WHERE (((A.EEID)<>[b].[eeid]))
ORDER BY A.EEID, A.Supervisor, A.screenNumber;

with data

A.EEID	B.EEID	Supervisor	screenNumber
Jones	Smith	White	1
Smith	Jones	White	1

2)Then return all records that are not in the qryCommon
qryUnCommon

SELECT tblEmpScreens.EEID, tblEmpScreens.Supervisor, tblEmpScreens.screenNumber
FROM tblEmpScreens LEFT JOIN qryCommon ON (tblEmpScreens.EEID = qryCommon.A.EEID) AND (tblEmpScreens.Supervisor = qryCommon.Supervisor) AND (tblEmpScreens.screenNumber = qryCommon.screenNumber)
WHERE (((qryCommon.A.EEID) Is Null))
ORDER BY tblEmpScreens.EEID, tblEmpScreens.screenNumber;

with data
EEID	Supervisor	screenNumber
Brown	Black	1
Jones	White	3
Jones	White	4
Smith	White	2

 

[cdgeer]

Thanks MAJP! Your Solution worked perfectly!