Joining 2 Queries

D

DS

I have two queries that I want to join. I want to use SQL so that I can
eliminate the queries how would I do this?

Query 6
SELECT tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblChecks.ChkTaxCodeID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, tblChecks.ChkPrinted, tblChecks.ChkDividedCheck,
tblChecks.ChkTypeID, tblChecks.ChkCancelled, tblChecks.ChkFXSep
FROM tblChecks
GROUP BY tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblChecks.ChkTaxCodeID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, tblChecks.ChkPrinted, tblChecks.ChkDividedCheck,
tblChecks.ChkTypeID, tblChecks.ChkCancelled, tblChecks.ChkFXSep
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkDividedCheck)=0) AND ((tblChecks.ChkTypeID) Not Like 5) AND
((tblChecks.ChkCancelled)=0) AND ((tblChecks.ChkFXSep)=0));


Query 7
SELECT Query6.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP
FROM Query6 INNER JOIN tblEmployees ON Query6.ChkServer =
tblEmployees.EmployeeID
GROUP BY Query6.ChkServer, [EmpFirstName] & " " & [EmpLastName]
ORDER BY [EmpFirstName] & " " & [EmpLastName];

Thanks
DS
 
D

DS

I came up with this but it's still not returning values.

With Me.ListAll
.RowSource = "SELECT tblChecks.ChkTabID, tblChecks.ChkAlias,
tblChecks.CheckID, tblChecks.ChkTaxCodeID, " & _
"tblChecks.ChkGuests, tblChecks.ChkPaid, tblChecks.ChkServer,
tblChecks.ChkPrinted, " & _
"tblChecks.ChkDividedCheck, tblChecks.ChkTypeID, tblChecks.ChkCancelled,
tblChecks.ChkFXSep " & _
"FROM tblChecks " & _
"GROUP BY tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblChecks.ChkTaxCodeID, " & _
"tblChecks.ChkGuests, tblChecks.ChkPaid, tblChecks.ChkServer,
tblChecks.ChkPrinted, " & _
"tblChecks.ChkDividedCheck, tblChecks.ChkTypeID, tblChecks.ChkCancelled,
tblChecks.ChkFXSep " & _
"HAVING (((tblChecks.ChkPaid)=0) " & _
"AND ((tblChecks.ChkPrinted)=0) " & _
"AND ((tblChecks.ChkDividedCheck)=0) " & _
"AND ((tblChecks.ChkTypeID) Not Like 5) " & _
"AND ((tblChecks.ChkCancelled)=0) " & _
"AND ((tblChecks.ChkFXSep)=0)) " & _
"UNION " & _
"SELECT tblEmployees.EmployeeID, [EmpFirstName] & "" "" & [EmpLastName]
AS EMP " & _
"FROM tblEmployees " & _
"GROUP BY tblEmployees.EmployeeID, [EmpFirstName] & "" "" &
[EmpLastName] " & _
"ORDER BY [EmpFirstName] & "" "" & [EmpLastName];"
.ColumnCount = 16
.ColumnWidths = ".2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2
in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in;.2 in"
.Requery
End With
 
J

John Spencer

SELECT DISTINCT tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblChecks.ChkTaxCodeID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, tblChecks.ChkPrinted, tblChecks.ChkDividedCheck,
tblChecks.ChkTypeID, tblChecks.ChkCancelled, tblChecks.ChkFXSep
FROM tblChecks
WHERE tblChecks.ChkPaid=0 AND tblChecks.ChkPrinted=0 AND
tblChecks.ChkDividedCheck=0 AND tblChecks.ChkTypeID <> 5 AND
tblChecks.ChkCancelled=0 AND tblChecks.ChkFXSep=0
It looks like all you really need is the following.

Query 7
SELECT Distinct Query6.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS
EMP
FROM
(
SELECT DISTINCT
tblChecks.ChkServer
FROM tblChecks
WHERE tblChecks.ChkPaid=0 AND tblChecks.ChkPrinted=0 AND
tblChecks.ChkDividedCheck=0 AND tblChecks.ChkTypeID <> 5 AND
tblChecks.ChkCancelled=0 AND tblChecks.ChkFXSep=0) AS Query6

INNER JOIN tblEmployees ON Query6.ChkServer =
tblEmployees.EmployeeID
ORDER BY [EmpFirstName] & " " & [EmpLastName];

Which could probably be made even simpler and be the following

SELECT DISTINCT tblChecks.ChkServer
, [EmpFirstName] & " " & [EmpLastName] AS EMP

FROM tblChecks INNER JOIN tblEmployees
ON tblChecks.ChkServer = tblEmployees.ChkServer

WHERE tblChecks.ChkPaid=0 AND tblChecks.ChkPrinted=0 AND
tblChecks.ChkDividedCheck=0 AND tblChecks.ChkTypeID <> 5 AND
tblChecks.ChkCancelled=0 AND tblChecks.ChkFXSep=0

ORDER BY [EmpFirstName] & " " & [EmpLastName];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
D

DS

Thank You, Thank You, Thank You !
This was quite a bear for me to handle! Either one of the statements that
you wrote worked fine.
Once again,
Thank you
DS
John Spencer said:
SELECT DISTINCT tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblChecks.ChkTaxCodeID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, tblChecks.ChkPrinted, tblChecks.ChkDividedCheck,
tblChecks.ChkTypeID, tblChecks.ChkCancelled, tblChecks.ChkFXSep
FROM tblChecks
WHERE tblChecks.ChkPaid=0 AND tblChecks.ChkPrinted=0 AND
tblChecks.ChkDividedCheck=0 AND tblChecks.ChkTypeID <> 5 AND
tblChecks.ChkCancelled=0 AND tblChecks.ChkFXSep=0
It looks like all you really need is the following.

Query 7
SELECT Distinct Query6.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS
EMP
FROM
(
SELECT DISTINCT
tblChecks.ChkServer
FROM tblChecks
WHERE tblChecks.ChkPaid=0 AND tblChecks.ChkPrinted=0 AND
tblChecks.ChkDividedCheck=0 AND tblChecks.ChkTypeID <> 5 AND
tblChecks.ChkCancelled=0 AND tblChecks.ChkFXSep=0) AS Query6

INNER JOIN tblEmployees ON Query6.ChkServer =
tblEmployees.EmployeeID
ORDER BY [EmpFirstName] & " " & [EmpLastName];

Which could probably be made even simpler and be the following

SELECT DISTINCT tblChecks.ChkServer
, [EmpFirstName] & " " & [EmpLastName] AS EMP

FROM tblChecks INNER JOIN tblEmployees
ON tblChecks.ChkServer = tblEmployees.ChkServer

WHERE tblChecks.ChkPaid=0 AND tblChecks.ChkPrinted=0 AND
tblChecks.ChkDividedCheck=0 AND tblChecks.ChkTypeID <> 5 AND
tblChecks.ChkCancelled=0 AND tblChecks.ChkFXSep=0

ORDER BY [EmpFirstName] & " " & [EmpLastName];

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

DS said:
I have two queries that I want to join. I want to use SQL so that I can
eliminate the queries how would I do this?

Query 6
SELECT tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblChecks.ChkTaxCodeID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, tblChecks.ChkPrinted, tblChecks.ChkDividedCheck,
tblChecks.ChkTypeID, tblChecks.ChkCancelled, tblChecks.ChkFXSep
FROM tblChecks
GROUP BY tblChecks.ChkTabID, tblChecks.ChkAlias, tblChecks.CheckID,
tblChecks.ChkTaxCodeID, tblChecks.ChkGuests, tblChecks.ChkPaid,
tblChecks.ChkServer, tblChecks.ChkPrinted, tblChecks.ChkDividedCheck,
tblChecks.ChkTypeID, tblChecks.ChkCancelled, tblChecks.ChkFXSep
HAVING (((tblChecks.ChkPaid)=0) AND ((tblChecks.ChkPrinted)=0) AND
((tblChecks.ChkDividedCheck)=0) AND ((tblChecks.ChkTypeID) Not Like 5)
AND ((tblChecks.ChkCancelled)=0) AND ((tblChecks.ChkFXSep)=0));


Query 7
SELECT Query6.ChkServer, [EmpFirstName] & " " & [EmpLastName] AS EMP
FROM Query6 INNER JOIN tblEmployees ON Query6.ChkServer =
tblEmployees.EmployeeID
GROUP BY Query6.ChkServer, [EmpFirstName] & " " & [EmpLastName]
ORDER BY [EmpFirstName] & " " & [EmpLastName];

Thanks
DS
Click to expand...
Click to expand...
 

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

ORDER BY Problem 4
UNION QUERY (Sorting) 2
UNION SELECT Problem 2
UnMatched Query 3
From External Database 5
Same data from 2 sources 2
SubQuery Problem 1
Joining two queries 2

Top