DS said:Sure, Thanks Doug.
I have this subquery thing happening, and once again I'm having a tuff
time.
Here are the 2 queries.
Query13
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
This is what I want.......
Query12
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
Emp, tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON tblJobNames.JobNameID
= tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not
Like 3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is
Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
What I'd like to do is eliminate the Queries and do this strictly SQL,
right now it's just SQL from the Query grid.
Once again,
Thank you,
DS
Douglas J. Steele said:Can you give some idea of what it is you're trying to do?
Douglas J. Steele said:What's wrong with having two queries?
Try:
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
Emp,
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN
(SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))) AS Query13
ON tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON tblJobNames.JobNameID
=
tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not
Like
3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
DS said:Sure, Thanks Doug.
I have this subquery thing happening, and once again I'm having a tuff
time.
Here are the 2 queries.
Query13
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
This is what I want.......
Query12
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
Emp, tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON tblJobNames.JobNameID
= tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not
Like 3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is
Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
What I'd like to do is eliminate the Queries and do this strictly SQL,
right now it's just SQL from the Query grid.
Once again,
Thank you,
DS
Douglas J. Steele said:Can you give some idea of what it is you're trying to do?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
This is just a starting point but I need to run 2 queries I'm guessing.
The first query would tell me what records not to include in the second
query. Are there any examples of this?
Thanks
DS
DS said:Nothing I guess, I'm just trying to expand my horizons and learn how to do
these subqueries. So if I can get the reference to Query1 out of there
completely and replac eit with code I would be very excited, happy and
considered expanded!
Thanks
DS
Douglas J. Steele said:What's wrong with having two queries?
Try:
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
Emp,
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN
(SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))) AS Query13
ON tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON tblJobNames.JobNameID
=
tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not
Like
3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
DS said:Sure, Thanks Doug.
I have this subquery thing happening, and once again I'm having a tuff
time.
Here are the 2 queries.
Query13
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
This is what I want.......
Query12
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
Emp, tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON
tblJobNames.JobNameID = tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut, Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not
Like 3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is
Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
What I'd like to do is eliminate the Queries and do this strictly SQL,
right now it's just SQL from the Query grid.
Once again,
Thank you,
DS
Can you give some idea of what it is you're trying to do?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
This is just a starting point but I need to run 2 queries I'm
guessing. The first query would tell me what records not to include in
the second query. Are there any examples of this?
Thanks
DS
Sure, Thanks Doug.
I have this subquery thing happening, and once again I'm having a tuff time.
Here are the 2 queries.
Query13
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
This is what I want.......
Query12
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS Emp,
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON tblJobNames.JobNameID =
tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not Like
3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
What I'd like to do is eliminate the Queries and do this strictly SQL, right
now it's just SQL from the Query grid.
Once again,
Thank you,
DS
Douglas J. Steele said:Can you give some idea of what it is you're trying to do?
Douglas J. Steele said:So did my suggested query work for you?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
DS said:Nothing I guess, I'm just trying to expand my horizons and learn how to
do these subqueries. So if I can get the reference to Query1 out of
there completely and replac eit with code I would be very excited, happy
and considered expanded!
Thanks
DS
Douglas J. Steele said:What's wrong with having two queries?
Try:
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
Emp,
tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut,
Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN
(SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS
EMP,
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))) AS
Query13
ON tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON
tblJobNames.JobNameID =
tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut,
Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not
Like
3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is
Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Sure, Thanks Doug.
I have this subquery thing happening, and once again I'm having a tuff
time.
Here are the 2 queries.
Query13
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS
EMP, tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
This is what I want.......
Query12
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
Emp, tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON
tblJobNames.JobNameID = tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut, Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not
Like 3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is
Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
What I'd like to do is eliminate the Queries and do this strictly SQL,
right now it's just SQL from the Query grid.
Once again,
Thank you,
DS
message Can you give some idea of what it is you're trying to do?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
This is just a starting point but I need to run 2 queries I'm
guessing. The first query would tell me what records not to include
in the second query. Are there any examples of this?
Thanks
DS
John Spencer said:You might try something like the following. This uses a subquery that is
built on the basis of query 13.
In Access, you can't use a subquery that reguires square braces []
embedded in the subquery. Since your field and table names meet the
requirements you can simply paste the subquery into the query. It must be
surrounded by [] and given an alias. Also, the last brace must be
followed by a dot (period).
So I changed this
... tblEmployees LEFT JOIN Query13 ...
To this
... tblEmployees LEFT JOIN [].Query13 ...
and then did this
... tblEmployees LEFT JOIN [<Paste or type the subquery>].Query13 ...
I ended up with this after I cleaned up the query. Note that I removed
all the grouping and used DISTINCT since you were not performing any
aggregation. Also I dropped the ORDER By clause in "query13" since it
should be ignored in a subquery.
SELECT DISTINCT tblEmployees.EmployeeID
, [EmpFirstName] & " " & [EmpLastName] AS Emp
, tblEmployees.EmpSignedIN
, tblJobNames.JobType, tblEmployees.EmpCashedOut
, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees
LEFT JOIN [
SELECT tblChecks.ChkServer
, EmpFirstName & " " & EmpLastName AS EMP
, tblChecks.ChkPaid
, tblChecks.ChkPrinted
, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks
ON tblEmployees.EmployeeID = tblChecks.ChkServer
WHERE (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=0
AND tblChecks.ChkCancelled=-1)
OR (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=-1
AND tblChecks.ChkCancelled=0)
]. AS Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer)
INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID)
ON tblJobNames.JobNameID = tblJobsEmp.JobID
WHERE tblEmployees.EmpSignedIN=-1
AND tblJobNames.JobType <> 3
AND tblEmployees.EmpCashedOut=0
AND Query13.ChkServer Is Null
ORDER BY [EmpFirstName] & " " & [EmpLastName];
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Sure, Thanks Doug.
I have this subquery thing happening, and once again I'm having a tuff
time.
Here are the 2 queries.
Query13
SELECT tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP,
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.EmployeeID =
tblChecks.ChkServer
GROUP BY tblChecks.ChkServer, [EmpFirstName] & " " & [EmpLastName],
tblChecks.ChkPaid, tblChecks.ChkPrinted, tblChecks.ChkCancelled
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkCancelled)=-1)) OR (((tblChecks.ChkPaid)=0) AND
((tblChecks.ChkPrinted)=-1) AND ((tblChecks.ChkCancelled)=0))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
This is what I want.......
Query12
SELECT tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS
Emp, tblEmployees.EmpSignedIN, tblJobNames.JobType,
tblEmployees.EmpCashedOut, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees LEFT JOIN Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer) INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID) ON tblJobNames.JobNameID
= tblJobsEmp.JobID
GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & " " & [EmpLastName],
tblEmployees.EmpSignedIN, tblJobNames.JobType, tblEmployees.EmpCashedOut,
Query13.ChkServer
HAVING (((tblEmployees.EmpSignedIN)=-1) AND ((tblJobNames.JobType) Not
Like 3) AND ((tblEmployees.EmpCashedOut)=0) AND ((Query13.ChkServer) Is
Null))
ORDER BY [EmpFirstName] & " " & [EmpLastName];
What I'd like to do is eliminate the Queries and do this strictly SQL,
right now it's just SQL from the Query grid.
Once again,
Thank you,
DS
Douglas J. Steele said:Can you give some idea of what it is you're trying to do?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
This is just a starting point but I need to run 2 queries I'm guessing.
The first query would tell me what records not to include in the second
query. Are there any examples of this?
Thanks
DS
Great John that helps even further but how can I get rid of the reference to
Query 13 altogether and replace it with code? I'm really trying to learn
and understand this Sub-Query thing. Any and all help is appreciated!
I ended up with this after I cleaned up the query. Note that I removed
all the grouping and used DISTINCT since you were not performing any
aggregation. Also I dropped the ORDER By clause in "query13" since it
should be ignored in a subquery.
SELECT DISTINCT tblEmployees.EmployeeID
, [EmpFirstName] & " " & [EmpLastName] AS Emp
, tblEmployees.EmpSignedIN
, tblJobNames.JobType, tblEmployees.EmpCashedOut
, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees
LEFT JOIN [
SELECT tblChecks.ChkServer
, EmpFirstName & " " & EmpLastName AS EMP
, tblChecks.ChkPaid
, tblChecks.ChkPrinted
, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks
ON tblEmployees.EmployeeID = tblChecks.ChkServer
WHERE (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=0
AND tblChecks.ChkCancelled=-1)
OR (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=-1
AND tblChecks.ChkCancelled=0)
]. AS Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer)
INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID)
ON tblJobNames.JobNameID = tblJobsEmp.JobID
WHERE tblEmployees.EmpSignedIN=-1
AND tblJobNames.JobType <> 3
AND tblEmployees.EmpCashedOut=0
AND Query13.ChkServer Is Null
ORDER BY [EmpFirstName] & " " & [EmpLastName];
Marshall Barton said:John did get rid or Query13. It just happens to be the
alias name for the subquery, if it would help clarify things
for you, use XYZ or whatever you like instead.
--
Marsh
MVP [MS Access]
Great John that helps even further but how can I get rid of the reference
to
Query 13 altogether and replace it with code? I'm really trying to learn
and understand this Sub-Query thing. Any and all help is appreciated!
I ended up with this after I cleaned up the query. Note that I removed
all the grouping and used DISTINCT since you were not performing any
aggregation. Also I dropped the ORDER By clause in "query13" since it
should be ignored in a subquery.
SELECT DISTINCT tblEmployees.EmployeeID
, [EmpFirstName] & " " & [EmpLastName] AS Emp
, tblEmployees.EmpSignedIN
, tblJobNames.JobType, tblEmployees.EmpCashedOut
, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees
LEFT JOIN [
SELECT tblChecks.ChkServer
, EmpFirstName & " " & EmpLastName AS EMP
, tblChecks.ChkPaid
, tblChecks.ChkPrinted
, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks
ON tblEmployees.EmployeeID = tblChecks.ChkServer
WHERE (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=0
AND tblChecks.ChkCancelled=-1)
OR (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=-1
AND tblChecks.ChkCancelled=0)
]. AS Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer)
INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID)
ON tblJobNames.JobNameID = tblJobsEmp.JobID
WHERE tblEmployees.EmpSignedIN=-1
AND tblJobNames.JobType <> 3
AND tblEmployees.EmpCashedOut=0
AND Query13.ChkServer Is Null
ORDER BY [EmpFirstName] & " " & [EmpLastName];
DS said:Thanks! This is just getting confusing!!!
DS
Marshall Barton said:John did get rid or Query13. It just happens to be the
alias name for the subquery, if it would help clarify things
for you, use XYZ or whatever you like instead.
--
Marsh
MVP [MS Access]
Great John that helps even further but how can I get rid of the reference
to
Query 13 altogether and replace it with code? I'm really trying to learn
and understand this Sub-Query thing. Any and all help is appreciated!
I ended up with this after I cleaned up the query. Note that I removed
all the grouping and used DISTINCT since you were not performing any
aggregation. Also I dropped the ORDER By clause in "query13" since it
should be ignored in a subquery.
SELECT DISTINCT tblEmployees.EmployeeID
, [EmpFirstName] & " " & [EmpLastName] AS Emp
, tblEmployees.EmpSignedIN
, tblJobNames.JobType, tblEmployees.EmpCashedOut
, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees
LEFT JOIN [
SELECT tblChecks.ChkServer
, EmpFirstName & " " & EmpLastName AS EMP
, tblChecks.ChkPaid
, tblChecks.ChkPrinted
, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks
ON tblEmployees.EmployeeID = tblChecks.ChkServer
WHERE (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=0
AND tblChecks.ChkCancelled=-1)
OR (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=-1
AND tblChecks.ChkCancelled=0)
]. AS Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer)
INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID)
ON tblJobNames.JobNameID = tblJobsEmp.JobID
WHERE tblEmployees.EmpSignedIN=-1
AND tblJobNames.JobType <> 3
AND tblEmployees.EmpCashedOut=0
AND Query13.ChkServer Is Null
ORDER BY [EmpFirstName] & " " & [EmpLastName];
John Spencer said:To make it a little clearer, I've renamed the subquery to MySubQuery. I
was a bit lazy and just reused the **NAME** string "query13". You can
assign any name you want to the subquery.
Also, since the only value you really need from the subquery is
tblChecks.chkServer I dropped the other fields from the subquery.
SELECT DISTINCT tblEmployees.EmployeeID
, [EmpFirstName] & " " & [EmpLastName] AS Emp
, tblEmployees.EmpSignedIN
, tblJobNames.JobType, tblEmployees.EmpCashedOut
, MySubQuery.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees
LEFT JOIN [
SELECT tblChecks.ChkServer
FROM tblEmployees INNER JOIN tblChecks
ON tblEmployees.EmployeeID = tblChecks.ChkServer
WHERE (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=0
AND tblChecks.ChkCancelled=-1)
OR (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=-1
AND tblChecks.ChkCancelled=0)
]. AS MySubQuery ON
tblEmployees.EmployeeID = MySubQuery.ChkServer)
INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID)
ON tblJobNames.JobNameID = tblJobsEmp.JobID
WHERE tblEmployees.EmpSignedIN=-1
AND tblJobNames.JobType <> 3
AND tblEmployees.EmpCashedOut=0
AND MySubQuery.ChkServer Is Null
ORDER BY [EmpFirstName] & " " & [EmpLastName];
Of course, this whole thing could be rewritten to use a Not Exists
subquery in a where clause. But since you seemed to want to learn about
including a subquery in the FROM clause I stuck with your original
purpose.
Plus this query is probably much more efficient than a Not Exists
subquery.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
DS said:Thanks! This is just getting confusing!!!
DS
Marshall Barton said:John did get rid or Query13. It just happens to be the
alias name for the subquery, if it would help clarify things
for you, use XYZ or whatever you like instead.
--
Marsh
MVP [MS Access]
DS wrote:
Great John that helps even further but how can I get rid of the
reference to
Query 13 altogether and replace it with code? I'm really trying to
learn
and understand this Sub-Query thing. Any and all help is appreciated!
[]
I ended up with this after I cleaned up the query. Note that I
removed
all the grouping and used DISTINCT since you were not performing any
aggregation. Also I dropped the ORDER By clause in "query13" since it
should be ignored in a subquery.
SELECT DISTINCT tblEmployees.EmployeeID
, [EmpFirstName] & " " & [EmpLastName] AS Emp
, tblEmployees.EmpSignedIN
, tblJobNames.JobType, tblEmployees.EmpCashedOut
, Query13.ChkServer
FROM tblJobNames INNER JOIN ((tblEmployees
LEFT JOIN [
SELECT tblChecks.ChkServer
, EmpFirstName & " " & EmpLastName AS EMP
, tblChecks.ChkPaid
, tblChecks.ChkPrinted
, tblChecks.ChkCancelled
FROM tblEmployees INNER JOIN tblChecks
ON tblEmployees.EmployeeID = tblChecks.ChkServer
WHERE (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=0
AND tblChecks.ChkCancelled=-1)
OR (tblChecks.ChkPaid=0
AND tblChecks.ChkPrinted=-1
AND tblChecks.ChkCancelled=0)
]. AS Query13 ON
tblEmployees.EmployeeID = Query13.ChkServer)
INNER JOIN tblJobsEmp ON
tblEmployees.EmployeeID = tblJobsEmp.EmployeeID)
ON tblJobNames.JobNameID = tblJobsEmp.JobID
WHERE tblEmployees.EmpSignedIN=-1
AND tblJobNames.JobType <> 3
AND tblEmployees.EmpCashedOut=0
AND Query13.ChkServer Is Null
ORDER BY [EmpFirstName] & " " & [EmpLastName];