Pivot table for two queries (1 Viewer)

[Harris@Z]

Hi, hope someone may be able to advise!

I am trying to join two queries into a pivot table

This pivot on one query works:
TRANSFORM Count(SampleTestedFor.TestedFor) AS TestedFor
SELECT IIf([SampleTestedFor].[Packed]=8,'','LCMS') AS TestGroup
FROM ((Samples_List INNER JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) LEFT JOIN Test_Methods ON SampleTestedFor.UniqueMethod_ID = Test_Methods.UniqueMethod_ID) INNER JOIN Orders ON Samples_List.Order_ID = Orders.Order_ID
WHERE (((Orders.Order_received)>='2021-02-01' And (Orders.Order_received)<='2022-07-27') AND ((Test_Methods.TestGroupCode)=8 Or (Test_Methods.TestGroupCode)=9))
GROUP BY SampleTestedFor.Packed
PIVOT Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2);

However, adding a second does not "Syntax error (missing operator in query expression 'SampleTestedFor.Packed Union All ....."

TRANSFORM Count(SampleTestedFor.TestedFor) AS TestedFor
SELECT IIf([SampleTestedFor].[Packed]=8,'','LCMS') AS TestGroup
FROM ((Samples_List INNER JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) LEFT JOIN Test_Methods ON SampleTestedFor.UniqueMethod_ID = Test_Methods.UniqueMethod_ID) INNER JOIN Orders ON Samples_List.Order_ID = Orders.Order_ID
WHERE (((Orders.Order_received)>='2021-02-01' And (Orders.Order_received)<='2022-07-27') AND ((Test_Methods.TestGroupCode)=8 Or (Test_Methods.TestGroupCode)=9))
GROUP BY SampleTestedFor.Packed
Union All
SELECT IIf([SampleTestedFor].[Packed]=8,'','ELISA') AS TestGroup
FROM ((Samples_List INNER JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) LEFT JOIN Test_Methods ON SampleTestedFor.UniqueMethod_ID = Test_Methods.UniqueMethod_ID) INNER JOIN Orders ON Samples_List.Order_ID = Orders.Order_ID
WHERE (((Orders.Order_received)>='2021-02-01' And (Orders.Order_received)<='2022-07-27') AND ((Test_Methods.TestGroupCode)=1 Or (Test_Methods.TestGroupCode)=2))
GROUP BY SampleTestedFor.Packed
PIVOT Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2);

I am not able to ascertain what I am doing wrong, i.e., is there indeed a syntax error or is the code completely wrong.

Thanks
Harris

 

[arnelgp]

its better to save Each Select query into a Single query.
then make a Union query on the 2 queries.

 

[Harris@Z]

Thanks for your input!
So are you suggesting that I save one query as say, qry1, the second as qry2, and then create a pivot query incorporating qry1 and qry2?
(I am not an advanced programmer so not sure exactly what to do)

 

[arnelgp]

almost, you create and save each pivot query.
later union them.

EDIT: Just noticed your First Query does not have this before the word UNION:

PIVOT Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2);

 

[Harris@Z]

Thanks for your guidance, much appreciated.

I have created two queries and tried to link them, but not working.
I assume my syntax is at fault?
Could you please assist?

Joined query:
TRANSFORM Count
SELECT qryUnionELISA.Test, qryUnionELISA.Interval, qryUnionELISA.Count
FROM qryUnionELISA
UNION ALL
SELECT qryUnionLCMS.Test, qryUnionLCMS.Interval, qryUnionLCMS.Count
FROM qryUnionLCMS
PIVOT Interval

qryUnionELISA:
SELECT IIf([SampleTestedFor].[Packed]=8,'','ELISA') AS Test, Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2) AS [Interval], Count(SampleTestedFor.TestedFor) AS [Count]
FROM ((Samples_List INNER JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) LEFT JOIN Test_Methods ON SampleTestedFor.UniqueMethod_ID = Test_Methods.UniqueMethod_ID) INNER JOIN Orders ON Samples_List.Order_ID = Orders.Order_ID
WHERE (((Orders.Order_received)>='2022-04-27' And (Orders.Order_received)<='2022-07-27') AND ((Test_Methods.TestGroupCode)=1 Or (Test_Methods.TestGroupCode)=2 Or (Test_Methods.TestGroupCode)=3 Or (Test_Methods.TestGroupCode)=4 Or (Test_Methods.TestGroupCode)=5 Or (Test_Methods.TestGroupCode) Is Null))
GROUP BY Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2), SampleTestedFor.Packed;

qryUnionLCMS
SELECT IIf([SampleTestedFor].[Packed]=8,'','LCMS') AS Test, Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2) AS [Interval], Count(SampleTestedFor.TestedFor) AS [Count]
FROM ((Samples_List INNER JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) LEFT JOIN Test_Methods ON SampleTestedFor.UniqueMethod_ID = Test_Methods.UniqueMethod_ID) INNER JOIN Orders ON Samples_List.Order_ID = Orders.Order_ID
WHERE (((Orders.Order_received)>='2022-04-27' And (Orders.Order_received)<='2022-07-27') AND ((Test_Methods.TestGroupCode)=8 Or (Test_Methods.TestGroupCode)=9))
GROUP BY Format([Order_received],'yyyy') & " " & Right('0' & Format([Order_received],'mm'),2), SampleTestedFor.Packed;

 

[Pat Hartman]

You have to create the union querydef and save it. Then use that union querydef to create the crosstab. Only Select queries can be used in a crosstab.

Are you sure you want Union All and not just Union?

 

[plog]

Cook then eat your elephant in bites. You're trying to kill, cook and swallow in one motion.

UNION and TRANSFORM queries should only UNION and TRANSFORM. No logic, no calculations. When you add those to the mix it makes it so hard to find out where it goes wrong.

So the first step in all of this is to get your SELECT queries correct. Throw your logic in there, calculate your Test field in there. Then run and make sure it works. Once it does save those queries as sub1 and sub2.

Next step is to UNION those queries together. This becomes trivial because you are simply doing a UNION of 2 queries and nothing else. Run that query to make sure it works and name it 'sub3'.

Finally, make another query from sub3 and TRANSFORM it. Again, simple because all you are doing is pivoting that data, nothing else. Run that and you have your final results.

 

[Harris@Z]

Thanks all for your input, and in particular to plog who reminded me to keep it simple.
Tried your approach and got it working perfectly!

Thanks so much, your help has been tremendous and is greatly appreciated.