Sep 12 2022 05:45 PM
Experts,
I am trying to join 2 queries into a Union.
I am getting a syntax in the following:
Do you see where I am wrong? The queries work separately.
thank you very much.
here is my sql:
TRANSFORM Sum(qryLCAmends.AmountOfChange) AS SumOfAmountOfChange1
SELECT tblCompanies.CompanyName, qryLCAmends.LCName, DLookUp("ExpiredYN","tblLetterOfCredit","[LCID] = " & [LetterOfCreditID]) AS Status
FROM qryLCAmends LEFT JOIN tblCompanies ON qryLCAmends.CoID = tblCompanies.CoID
WHERE (((qryLCAmends.ProjectID)=3))
GROUP BY tblCompanies.CompanyName, qryLCAmends.LCName, DLookUp("ExpiredYN","tblLetterOfCredit","[LCID] = " & [LetterOfCreditID]), qryLCAmends.ProjectID, qryLCAmends.LCNo
PIVOT Format([DateAmendSentToBank],"yyyy-mm")
UNION ALL
TRANSFORM Sum(IIf([Balance]=0,Null,[Balance])) AS BalanceZ
SELECT tblCompanies.CompanyName, qryLCAmends.LCName, DLookUp("ExpiredYN","tblLetterOfCredit","[LCID] = " & [LetterOfCreditID]) AS Status
FROM qryLCAmends LEFT JOIN tblCompanies ON qryLCAmends.CoID = tblCompanies.CoID
WHERE (((qryLCAmends.ProjectID)=3))
GROUP BY tblCompanies.CompanyName, qryLCAmends.LCName, DLookUp("ExpiredYN","tblLetterOfCredit","[LCID] = " & [LetterOfCreditID]), qryLCAmends.ProjectID, qryLCAmends.LCNo
PIVOT Format([DateAmendSentToBank],"yyyy-mm");
Sep 13 2022 12:16 AM - edited Sep 13 2022 12:16 AM
Solutionyou can try this.
save Each Crosstab in its own Query.
then Select them to create a Union Query:
Select * From CrosstabQuery1
UNION
Select * From CrosstabQuery2;
Note that both query should have exactly The Same Number of Columns.
Sep 13 2022 12:19 AM
Hi,
Maybe it just doesn't work out of timing/processing reasons because of the "dynamic" character of the crosstab SQL. Try to save the two queries and use e.g.
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
or a bit shorter
TABLE Query1
UNION ALL
TABLE Query2
Servus
Karl
Access News
Access DevCon
Sep 14 2022 02:46 AM
Sep 13 2022 12:16 AM - edited Sep 13 2022 12:16 AM
Solutionyou can try this.
save Each Crosstab in its own Query.
then Select them to create a Union Query:
Select * From CrosstabQuery1
UNION
Select * From CrosstabQuery2;
Note that both query should have exactly The Same Number of Columns.