UnMatched Query

D

DS

I want to do an unmatched Query, If the Employee appears in the first
Query I don't want it in the second Query.



QueryUnPaid
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkCancelled, tblChecks.ChkPaid
FROM tblChecks INNER JOIN tblEmployees ON
tblChecks.ChkServer=tblEmployees.EmployeeID
WHERE ((tblChecks.ChkPaid)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];


QueryReady
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
EMP, tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut
FROM tblEmployees
WHERE (((tblEmployees.EmpSignedIN)=0) AND ((tblEmployees.EmpCashedOut)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];

Any Help appreciated,
Thanks
DS
 
W

Wolfgang Kais

Hello "DS".

DS said:
I want to do an unmatched Query, If the Employee appears in the
first Query I don't want it in the second Query.

QueryUnPaid
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkCancelled,
tblChecks.ChkPaid FROM tblChecks INNER JOIN tblEmployees ON
tblChecks.ChkServer=tblEmployees.EmployeeID
WHERE ((tblChecks.ChkPaid)=0)
ORDER BY [EmpFirstName] & " " & [EmpLastName];


QueryReady
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
EMP,
tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut FROM tblEmployees
WHERE (((tblEmployees.EmpSignedIN)=0) AND ((tblEmployees.EmpCashedOut)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];

Any Help appreciated,
Thanks
DS

SELECT tblEmployees.EmployeeID,
[EmpFirstName] & " " & [EmpLastName] AS EMP,
tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut
FROM tblEmployees WHERE
(((tblEmployees.EmpSignedIN)=0) AND ((tblEmployees.EmpCashedOut)=0)
AND NOT EXISTS (SELECT * FROM tblChecks WHERE (tblChecks.ChkPaid=0)
AND (tblChecks.ChkServer=tblEmployees.EmployeeID)))
ORDER BY [EmpFirstName], [EmpLastName];
 
D

DS

Wolfgang said:
Hello "DS".

DS said:
I want to do an unmatched Query, If the Employee appears in the
first Query I don't want it in the second Query.

QueryUnPaid
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkCancelled,
tblChecks.ChkPaid FROM tblChecks INNER JOIN tblEmployees ON
tblChecks.ChkServer=tblEmployees.EmployeeID
WHERE ((tblChecks.ChkPaid)=0)
ORDER BY [EmpFirstName] & " " & [EmpLastName];


QueryReady
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
EMP,
tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut FROM tblEmployees
WHERE (((tblEmployees.EmpSignedIN)=0) AND ((tblEmployees.EmpCashedOut)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];

Any Help appreciated,
Thanks
DS


SELECT tblEmployees.EmployeeID,
[EmpFirstName] & " " & [EmpLastName] AS EMP,
tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut
FROM tblEmployees WHERE
(((tblEmployees.EmpSignedIN)=0) AND ((tblEmployees.EmpCashedOut)=0)
AND NOT EXISTS (SELECT * FROM tblChecks WHERE (tblChecks.ChkPaid=0)
AND (tblChecks.ChkServer=tblEmployees.EmployeeID)))
ORDER BY [EmpFirstName], [EmpLastName];
Thanks Wolfgang, I tweaked it a bit and got...

With Me.ListEmp
..RowSource = "SELECT tblEmployees.EmployeeID, [EmpFirstName] & "" "" &
[EmpLastName] AS EMP, " & _
"tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut " & _
"FROM tblEmployees " & _
"WHERE (((tblEmployees.EmployeeID) Not In (SELECT tblChecks.ChkServer " & _
"FROM tblChecks INNER JOIN tblEmployees ON tblChecks.ChkServer =
tblEmployees.EmployeeID " & _
"WHERE (((tblChecks.ChkCancelled) = 0) " & _
"And ((tblChecks.ChkPaid) = 0) " & _
"And ((tblEmployees.EmpSignedIN) = -1) " & _
"And ((tblEmployees.EmpCashedOut) = 0)) " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName])) " & _
"AND ((tblEmployees.EmpSignedIN)=-1) " & _
"AND ((tblEmployees.EmpCashedOut)=0));"
..ColumnCount = 4
..ColumnWidths = "0 in;2 in;0 in;0 in"
..Requery
End With
 
D

DS

Wolfgang said:
Hello "DS".

DS said:
I want to do an unmatched Query, If the Employee appears in the
first Query I don't want it in the second Query.

QueryUnPaid
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkCancelled,
tblChecks.ChkPaid FROM tblChecks INNER JOIN tblEmployees ON
tblChecks.ChkServer=tblEmployees.EmployeeID
WHERE ((tblChecks.ChkPaid)=0)
ORDER BY [EmpFirstName] & " " & [EmpLastName];


QueryReady
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
EMP,
tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut FROM tblEmployees
WHERE (((tblEmployees.EmpSignedIN)=0) AND ((tblEmployees.EmpCashedOut)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];

Any Help appreciated,
Thanks
DS


SELECT tblEmployees.EmployeeID,
[EmpFirstName] & " " & [EmpLastName] AS EMP,
tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut
FROM tblEmployees WHERE
(((tblEmployees.EmpSignedIN)=0) AND ((tblEmployees.EmpCashedOut)=0)
AND NOT EXISTS (SELECT * FROM tblChecks WHERE (tblChecks.ChkPaid=0)
AND (tblChecks.ChkServer=tblEmployees.EmployeeID)))
ORDER BY [EmpFirstName], [EmpLastName];
Thanks Wolfgang, I tweaked it a bit and got.

With Me.ListEmp
..RowSource = "SELECT tblEmployees.EmployeeID, [EmpFirstName] & "" "" &
[EmpLastName] AS EMP, " & _
"tblEmployees.EmpSignedIN, tblEmployees.EmpCashedOut " & _
"FROM tblEmployees " & _
"WHERE (((tblEmployees.EmployeeID) Not In (SELECT tblChecks.ChkServer " & _
"FROM tblChecks INNER JOIN tblEmployees ON tblChecks.ChkServer =
tblEmployees.EmployeeID " & _
"WHERE (((tblChecks.ChkCancelled) = 0) " & _
"And ((tblChecks.ChkPaid) = 0) " & _
"And ((tblEmployees.EmpSignedIN) = -1) " & _
"And ((tblEmployees.EmpCashedOut) = 0)) " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName])) " & _
"AND ((tblEmployees.EmpSignedIN)=-1) " & _
"AND ((tblEmployees.EmpCashedOut)=0));"
..ColumnCount = 4
..ColumnWidths = "0 in;2 in;0 in;0 in"
..Requery
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Joining 2 Queries 3
UNION QUERY (Sorting) 2
UNION SELECT Problem 2
SubQuery Problem 1
DLookUp in a Query 2
From External Database 5
Same data from 2 sources 2
ORDER BY Problem 4

Top