UnMatched Query

  • Thread starter Thread starter DS
  • Start date Start date
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
 
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
Click to expand...

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];
 
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
Click to expand...


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];
Click to expand...
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
 
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
Click to expand...


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];
Click to expand...
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
 
Back
Top