Union Crosstab querie

[Gismo]

Hi all,

Is it possible to make a union query from 2 other cross tab queries?

 

[plog]

Yes, but you should probably UNION then cross-tab.

 

[theDBguy]

I don't see why not. I just gave it a try, and it seemed to work.

 

[Gismo]

Yes, but you should probably UNION then cross-tab.

I tried the below code but got a lot of errors

TRANSFORM Sum([Stock on Hand Qry - Manufactured].Qty) AS SumOfQty
SELECT [Stock on Hand Qry - Manufactured].Product, Sum([Stock on Hand Qry - Manufactured].Qty) AS [Total Of Qty]
FROM [Stock on Hand Qry - Manufactured]
GROUP BY [Stock on Hand Qry - Manufactured].Product
PIVOT [Stock on Hand Qry - Manufactured].[Stock Location]


Union

TRANSFORM Sum([Stock on Hand Qry - Manufactured].Volume) AS SumOfVolume
SELECT [Stock on Hand Qry - Manufactured].Product, Sum([Stock on Hand Qry - Manufactured].Volume) AS [Total Of Volume]
FROM [Stock on Hand Qry - Manufactured]
GROUP BY [Stock on Hand Qry - Manufactured].Product
PIVOT [Stock on Hand Qry - Manufactured].[Stock Location];

 

[Gismo]

Oh yes, forgot to metion
One cross tab querie is for Qty and the other is for Volume
I suppose I need to make provision for the dummy control in each query

 

[Gismo]

This is my attempt

TRANSFORM Sum([Stock on Hand Qry - Manufactured].Qty) AS SumOfQty
SELECT [Stock on Hand Qry - Manufactured].Product, Sum([Stock on Hand Qry - Manufactured].Qty) AS [Total Of Qty],"" As [Total Of Volume]
FROM [Stock on Hand Qry - Manufactured]
GROUP BY [Stock on Hand Qry - Manufactured].Product
PIVOT [Stock on Hand Qry - Manufactured].[Stock Location]


Union

TRANSFORM Sum([Stock on Hand Qry - Manufactured].Volume) AS SumOfVolume
SELECT [Stock on Hand Qry - Manufactured].Product,"" As [Total Of Qty], Sum([Stock on Hand Qry - Manufactured].Volume) AS [Total Of Volume]
FROM [Stock on Hand Qry - Manufactured]
GROUP BY [Stock on Hand Qry - Manufactured].Product
PIVOT [Stock on Hand Qry - Manufactured].[Stock Location];

 

[Pat Hartman]

So, what is wrong with the query? It is a crosstab. There is no union involved. If you are getting errors, just let the crosstab wizard build the query for you. You can only pivot ONE field. If you want to end up with both quantity and volume, then make two crosstab queries. The third query will JOIN the two crosstabs, NOT union them. That will give you both quantity and volume in one row.

If you really want them on separate rows, then the third query will union the other two crosstabs but you will need to add a dummy column so you can identify which row is quantity and which is volume since the column headers will be whatever you see in the first crosstab.

 

[June7]

Don't you want the Qty and Volume to be on same row for each Product? Review http://allenbrowne.com/ser-67.html#MultipleValues

Method not demonstrated in that article would involve a UNION first then CROSSTAB.

SELECT ID, Product, Location, Qty AS Data, "Qty" AS Src FROM table
UNION SELECT ID, Product, Location, Volume, "Vol" FROM table;

TRANSFORM Sum(Data) AS SumData
SELECT Product
FROM Query1
GROUP BY Product
PIVOT Src & Location;

However, UNION might perform slowly with large datasets.

If you need more assistance, show sample data as table in post or attach file.

 

[Pat Hartman]

Thanks. That's a slick example.

 

[Gismo]

Don't you want the Qty and Volume to be on same row for each Product? Review http://allenbrowne.com/ser-67.html#MultipleValues

Method not demonstrated in that article would involve a UNION first then CROSSTAB.

SELECT ID, Product, Location, Qty AS Data, "Qty" AS Src FROM table
UNION SELECT ID, Product, Location, Volume, "Vol" FROM table;

TRANSFORM Sum(Data) AS SumData
SELECT Product
FROM Query1
GROUP BY Product
PIVOT Src & Location;

However, UNION might perform slowly with large datasets.

If you need more assistance, show sample data as table in post or attach file.

Yes I need the Qty and the Volume in the same row

I am only working with about 100 line items, should not slow down that much I assume

I will give this a try

 

[Gismo]

Don't you want the Qty and Volume to be on same row for each Product? Review http://allenbrowne.com/ser-67.html#MultipleValues

Method not demonstrated in that article would involve a UNION first then CROSSTAB.

SELECT ID, Product, Location, Qty AS Data, "Qty" AS Src FROM table
UNION SELECT ID, Product, Location, Volume, "Vol" FROM table;

TRANSFORM Sum(Data) AS SumData
SELECT Product
FROM Query1
GROUP BY Product
PIVOT Src & Location;

However, UNION might perform slowly with large datasets.

If you need more assistance, show sample data as table in post or attach file.

When I do the 2 cross tab queries and the a union on the 2, I still get the same problem
Maybe I dont understand your explanation

TRANSFORM Sum([Stock on Hand Qry - Manufactured].Qty) AS SumOfQty
SELECT [Stock on Hand Qry - Manufactured].Product, Sum([Stock on Hand Qry - Manufactured].Qty),"" As Sum([Stock on Hand Qry - Manufactured].Volume)
FROM [Stock on Hand Qry - Manufactured]
GROUP BY [Stock on Hand Qry - Manufactured].Product
PIVOT [Stock on Hand Qry - Manufactured].[Stock Location]


Union


TRANSFORM Sum([Stock on Hand Qry - Manufactured].Volume) AS SumOfVolume
SELECT [Stock on Hand Qry - Manufactured].Product, Sum([Stock on Hand Qry - Manufactured].Volume),"" As Sum([Stock on Hand Qry - Manufactured].Qty)
FROM [Stock on Hand Qry - Manufactured]
GROUP BY [Stock on Hand Qry - Manufactured].Product
PIVOT [Stock on Hand Qry - Manufactured].[Stock Location];

 

[June7]

It works for me.

You have not done UNION and CROSSTAB according to my example. There is only one CROSSTAB in my example when UNION is used.

 

[ebs17]

@June7: Allen Browne's solution is a bright shining star, super and convincing. But only a few will be able to take it into their own hands and copy and implement the solution on their own. This is higher SQL art that not many imitate because they can't.

 

[June7]

Allen Brown's example does not use UNION. Mine does. Gismo said they were trying UNION approach but what they show is not following my example.

Allen's method uses a helper table and one CROSSTAB.

If you want to build two CROSSTABs then you would JOIN them, not UNION.

 

[Gismo]

It works for me.

You have not done UNION and CROSSTAB according to my example. There is only one CROSSTAB in my example when UNION is used.

Yeh, still not getting it

Attached is my DB

Please could you assist

 

[Gismo]

Allen Brown's example does not use UNION. Mine does. Gismo said they were trying UNION approach but what they show is not following my example.

Allen's method uses a helper table and one CROSSTAB.

If you want to build two CROSSTABs then you would JOIN them, not UNION.

By joining and not doing a union, would you still get the extra headings should the crosstab change?

 

[CJ_London]

I don’t see why you need a pivot, just an aggregate Query ?

 

[ebs17]

@June7:
Your suggestion is visibly correct and also meets the requirement.
However, some find it difficult to simply copy and try out and look at a solution with after all the given identifiers for tables and fields. That's life.

 

[arnelgp]

see Allen Brownes Crosstab sample, especially this section "

Multiple sets of values​

this is the example:

 

[June7]

Query1:
SELECT Product, [Stock Location], Qty AS Data, "Qty" AS Src, UOM FROM [Stock on Hand Qry - Manufactured]
UNION SELECT Product, [Stock Location], Volume, "Vol", UOM FROM [Stock on Hand Qry - Manufactured];

Query2:
TRANSFORM Sum(Query1.Data) AS SumOfData
SELECT Query1.Product, Query1.UOM
FROM Query1
GROUP BY Query1.Product, Query1.UOM
PIVOT [Src] & [Stock Location];