2 sql queries -> 1 pivottable

[timv]

Hi,
i have two queries, one for getting sales and one for getting orders. The data is in two different tables, the 'sales' table and the 'order' table.

They look like
SELECT customer, supplier, sum(quantity), article, price FROM sales GROUP BY customer, article, supplier
SELECT customer, supplier, sum(quantity), article, price FROM orders GROUP BY customer, article, supplier

Now, in a pivottable (in excel) i want the quantities sold compared with the quantities ordered. Combined for each customer, supplier, article,....

The data i want to have in my pivottable is e.g. the quantity sold and the quantity ordered of article x by customer y supplied by a supplier z.

I can't figure out how to put this data in one query. Or perhaps it's possible to have one pivottable based on multiple queries in excel?

I thought i could use the union command with an extra column type ('sale'/'order') but because of the trouble with the column names of the databases, their different meanings for sales/orders and because i need extra data (other columns that are in sales but not in orders and so on) i am looking for an other solution.

Can someone advice me on this?

grts,
timv

[erickwidya]

The data i want to have in my pivottable is e.g. the quantity sold and the quantity ordered of article x by customer y supplied by a supplier z.
maybe something like this?

TRANSFORM Table1.Customer
SELECT Table1.ProductNo
FROM Table1
GROUP BY Table1.ProductNo
PIVOT SUM(Table1.Quantity);

[timv]

i don't understand that query well,
i don't think i can use it in mysql, can i?

greets,

tim

[erickwidya]

well it's call Crosstab Query
Let say u want to know how many Quantity that Customer has bought for certain Product..
so in Excel u can do like this one
Prod1 - Prod2 - Prod3 - ...
Erick 1 - 0 - 2 - ...
Timv 0 - 3 - 1 - ...

so the query would be something like this

TRANSFORM tblProduct.ProductID
SELECT tblCustomer.CustomerID
FROM tblProduct, tblCustomer
GROUP BY tblCustomer.CustomerID
PIVOT tblSaleDetails.SaleQuantity


i don't think i can use it in mysql, can i? not quite sure..it can work it Access..maybe at other Dbase too like mysql since i never used one before.

hope u understand

[timv]

yes,
thanx alot.

[erickwidya]

ur welcome timv