Combining 3 Queries-Help me Please..cant think anymore

[drober]

I have 3 Queries and want to combine some info to list all employees that do
not qualify for bonus
#1 Query:EmployeeInfo_qry (this query represents all employees that could
possibly qualify for bonus)
Some Fields:
AWID
PayrollID
LastName
FirstName
Title
HireDate
TeamName
#2 Query:1stQtrLOA_qry (this query show all employees that were on leave for
the entire 1st quarter therefore disqualifying them for bonus)
Some Fields:
DateID
AWID
PayrollID
LastName
FirstName
LOA
#3 Query: 1stQtrAD_qry (this query show all employees that are on written
warnings for either attendance or code of conduct therefore disqualifying
them for bonus)
Some Fields:
DateID
AWID
PayrollID
LastName
FirstName
CaseTrack
The Results I need are:
Fields:
AWID
PayrollID
LastName
FirstName
Title
TeamName
LOA
CaseTrack

Help me please

 

[Jeff Boyce]

It appears you want to find employees in Q1 that are not in Q2, and
employees in Q1 that are not in Q3.

Access' query wizard can help you build an "unmatched" query (or two).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[drober]

eThe wizard only give me the chose of 2 queries; when I tried it with 2 of
the queries I did not get any results.

I want to compare Query 2 and Query 3 to Query 1 and give me all the
employees that are in Query 1 that are in match in Query 2 and any that are
match in Query 3.

I hope I am explaining this correctly.

 

[John W. Vinson]

Try something like

SELECT * FROM EmployeeInfo_qry
WHERE NOT EXISTS(SELECT AWID FROM 1stQtrLOA_Qry WHERE 1stQtrLOA_Qry.AWID =
EmployeeInfo_qry.AWID)
AND NOT EXISTS((SELECT AWID FROM 1stQtrAD_Qry WHERE 1stQtrAD_Qry.AWID =
EmployeeInfo_qry.AWID);

You might need PayrollID instead of AWID, I can't tell from your post which
field would identify an employee.

 

[drober]

John

I get Syntax error. in query expression 'NOT EXISTS(SELECT PayrollID FROM
1stQtrLOA_qry WHERE 1stQtrLOA_qry.PayrollID=EmployeeInfo_qry.PayrollID)AND
NOT EXISTS(SELECT PayrollID FROM 1stQtrAD_qry WHERE
1stQtrAD_qry.PayrollID=EmployeeInfo_qry.PayrollID);

I changed AWID to PayrollID because it does id my employees.

Thanks for you help

 

[Jeff Boyce]

I may not be understanding your description...

It sounded like the folks in Q3 are disqualified, as are the folks in Q2.
Why would you want the folks that ARE in Q2 AND Q3, if what you are trying
to do is find folks who are NOT disqualified?

What am I missing...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I want to compare Query 2 and Query 3 to Query 1 and give me all the
employees that are in Query 1 that are in match in Query 2 and any that are
match in Query 3.

Ok... change my NOT EXISTS to EXISTS:

SELECT * FROM EmployeeInfo_qry
WHERE NOT EXISTS(SELECT AWID FROM 1stQtrLOA_Qry WHERE 1stQtrLOA_Qry.AWID =
EmployeeInfo_qry.AWID)
AND NOT EXISTS((SELECT AWID FROM 1stQtrAD_Qry WHERE 1stQtrAD_Qry.AWID =
EmployeeInfo_qry.AWID);

or use a Join: create a new query by adding all three queries to the query
design window; join EmployeeInfo_qry to each of the other queries by the
unique employee ID.

 

[drober]

John
I still get a syntax error. I tried all three queries but I get all
employees and when I try to isolate just to LOA and CaseTrack then I get no
employees.
I am losing it. ; )

 

[drober]

Jeff

I want to compare Queries 2 & 3 to Query 1; so I can have a list of all
employees who do not qualify because were on Leave (query 2) and/or on a
Written Warning (query3).
FYI: queries 2 and 3 could have employees listed that are no longer employed
that's the reason for matching to Query 1 which is all current employees who
possibly qualify for bonus.
So my results would give me a report that explains why an employee does not
qualify for bonus ie LOA (leave) or CaseTrack (written warning)
thx

 

[John W. Vinson]

John

I get Syntax error. in query expression

Try

SELECT * FROM EmployeeInfo_qry
WHERE EXISTS
(SELECT PayrollID FROM 1stQtrLOA_Qry
WHERE 1stQtrLOA_Qry.PayrollID=EmployeeInfo_qry.PayrollID )
OR EXISTS
(SELECT PayrollID FROM 1stQtrAD_Qry
WHERE 1stQtrAD_Qry.PayrollID = EmployeeInfo_qry.PayrollID );

 

[John Spencer]

Use a UNION Query to get the list of employees that were on leave or warned or
both.

SELECT EmployeeID
FROM 1stQtrLOA_qry
UNION
SELECT EmployeeId
FROM 1stQtrAD_qry

Now use that to join to the list of all employees

SELECT E.*
FROM EmployeeInfo_qry as E INNER JOIN qUnionQuery as Q
On E.EmployeeID = Q.EmployeeID

You MIGHT be able to do that all in one query. UNTESTED option follows:

SELECT E.*
FROM EmployeeInfo_qry as E
WHERE EmployeeID in
(SELECT EmployeeID
FROM 1stQtrLOA_qry
UNION
SELECT EmployeeId
FROM 1stQtrAD_qry)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[drober]

John

You are AWESOME!!!! I was able to use the UNION Query then the join. I
have one last question and I am hoping you will be able to help.
The UNION Query is returning the EmployeeID can it also return the reason
from the LOA query (which is the LOA field ) and from the AD query (which is
the CaseTrack field).

Again Thank you SOOOOO Much. You are Greatly Appreciated!

 

[drober]

SELECT PayrollID, LOA
FROM e3rdQtrLOA_qry
UNION SELECT PayrollId, CaseTrack
FROM d3rdQtrAD_qry;

I used the above and it works PERFECTLY!! Again Thank you!!!