How could we have Nested crosstabs OR 3D Queries?

[myuser]

Hello everybody

Lets suppose we have
Table: products (As Attached)


I wrote the following query:


TRANSFORM Count(prod_ID)
SELECT seller,Count(prod_ID) AS Totally
FROM products
GROUP BY seller
PIVOT color;

It gives "Query1" result (As Attached)



Now, How could I get "Query2" result? (Also Attached)


Thank you in advance

 

[plog]

You can't. What you would need to do is spit your data out to excel and then use a Pivot Chart (http://www.excel-easy.com/examples/pivot-chart.html). Queries alone cannot produce what you want.

 

[JHB]

Is it always four colour, ("Black", "Blue", "Red" & "Yellow")?
And always tree types, ("Metal", "Plastic" & "Wood")?
Then you could use a recordset and some VBA code to insert it a predefined table and form/report.
Else you've to create your own "result table" on the fly using some VBA code + creating form/report.

 

[myuser]

Thank you plog , but I am sure it is possible to have nested queries. Maybe you have'nt got what I mean.


and thank you JHB
I have a big table about projects with many fields and many different data (more than 4 colors and more than 3 types)
I just made an easy example to make it sensible and this is just a miniaturized of which I need to do.

I don't care of the format of table in this example. but I just wanted to know about SQL commands to have nested transform-Pivot.

something like this scenario maybe:

1-saving the following query as "Query1":

TRANSFORM Count(prod_ID)
SELECT seller,Count(prod_ID) AS Totally
FROM products
GROUP BY seller
PIVOT color;


2-Then writing another query like this maybe:

TRANSFORM Count(prod_ID)
SELECT query1.seller , query1.Totally
FROM query
GROUP BY seller
PIVOT table_products.Type;


But there is something wrong.

Any idea?

 

[CJ_London]

TRANSFORM Count(prod_ID) 
SELECT seller,Count(prod_ID) AS Totally 
FROM products
GROUP BY seller
PIVOT color;

For a start, to use this you need to specify the columns e..g

...

PIVOT color in (Red, Green, Blue...);

 

[namliam]

Just to confirm, Plog is right, the exact result that you want is NOT possible in access.

You can do a merged column with color and type...
Select Color & "-" & Type as ColorType
from yourtable

and pivot on this (new) ColorType column, it will generate the numbers you want but NOT look the way you want it to in Query2