SOLVED

Join 2 Crosstabs using a UNION (Syntax)

Super Contributor

Experts,

I am trying to join 2 queries into a Union. 

I am getting a syntax in the following: 

Tony2021_0-1663029797753.png

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");

 

3 Replies
best response confirmed by Tony2021 (Super Contributor)
Solution

you 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.

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

Thank you Arnel and Karl. Your strategy worked!