One SELECT statement

[fl]

How do I combine the following two SELECT statements into one?

Is there a website out there that will show how to write better SQL
statements? I would like to learn to do without creating too many
queries to get my results. Thanks Vanderghast for showing me some
earlier.


SELECT DPXRQ001_XRV_SER_FUNC_SCTY.SER_ID_R,
DPXRQ001_XRV_SER_FUNC_SCTY.LGN_ID_R
FROM DPXRQ001_XRV_SER_FUNC_SCTY LEFT JOIN Query8 ON
(DPXRQ001_XRV_SER_FUNC_SCTY.SER_ID_R = Query8.SERIES) AND
(DPXRQ001_XRV_SER_FUNC_SCTY.LGN_ID_R = Query8.LogonID)
WHERE (((DPXRQ001_XRV_SER_FUNC_SCTY.SER_ID_R)="TICBC" Or
(DPXRQ001_XRV_SER_FUNC_SCTY.SER_ID_R)="TICBCSA"));

SELECT tblLogonID.LogonID, tblSerDef.SERIES, tblGroupID.GroupID
FROM (tblGroupID INNER JOIN tblLogonID ON tblGroupID.GroupID =
tblLogonID.GroupID) INNER JOIN tblSerDef ON tblGroupID.GroupID =
tblSerDef.GroupID
GROUP BY tblLogonID.LogonID, tblSerDef.SERIES, tblGroupID.GroupID
HAVING (((tblGroupID.GroupID)="TIC-TRAN"));

 

[Michel Walsh]

Hi,


If you want a vertical merge of all the records, I would suggest you add
a UNION ALL between the two SELECT.



SELECT DPXRQ001_XRV_SER_FUNC_SCTY.SER_ID_R As f1,
DPXRQ001_XRV_SER_FUNC_SCTY.LGN_ID_R As f2,
1 As f3
FROM DPXRQ001_XRV_SER_FUNC_SCTY LEFT JOIN Query8 ON
(DPXRQ001_XRV_SER_FUNC_SCTY.SER_ID_R = Query8.SERIES) AND
(DPXRQ001_XRV_SER_FUNC_SCTY.LGN_ID_R = Query8.LogonID)
WHERE (((DPXRQ001_XRV_SER_FUNC_SCTY.SER_ID_R)="TICBC" Or
(DPXRQ001_XRV_SER_FUNC_SCTY.SER_ID_R)="TICBCSA"))

UNION ALL

SELECT tblLogonID.LogonID, tblSerDef.SERIES, tblGroupID.GroupID, 2
FROM (tblGroupID INNER JOIN tblLogonID ON tblGroupID.GroupID =
tblLogonID.GroupID) INNER JOIN tblSerDef ON tblGroupID.GroupID =
tblSerDef.GroupID
GROUP BY tblLogonID.LogonID, tblSerDef.SERIES, tblGroupID.GroupID
HAVING (((tblGroupID.GroupID)="TIC-TRAN"));




The name of the fields would be the ones of the first select, here, f1, f2,
f3 (because of the aliases). I added a third field to allow you to identify
which SELECT contribute to the record, and so, you can use an ORDER BY, if
required, to sort the records accordingly. That is not mandatory, just
remove that third field if unnecessary.



Hoping it may help,
Vanderghast, Access MVP