MS Access: Crosstab query on multiple tables (many-to-many relation)

Question

I’m working with a MS Access database.

I have three tables :

Customers (idCustomer, CustomerName) :

1   C1
2   C2
3   C3
4   C4
5   C5

Products (idProduct, ProductName) :

1   P1
2   P2
3   P3
4   P4

Orders (id, idCustomer, idProduct, Quantity) :

1   2   1   10
2   2   2   15
3   4   2   13
4   5   4   19

I’m working on a query to get this result :

• Rows = Customers (all customers)
• Columns = Products (all products)
• Cells = Quantity

Like this :

        P1      P2      P3      P4
C1      0       0       0       0
C2      10      15      0       0
C3      0       0       0       0
C4      0       13      0       0
C5      0       0       0       19

Can anyone help on this query task?

Answer 1

The trick is:

1. First build a regular SELECT query, joining the 3 tables, that returns the columns you want (CustomerName, ProductName, Quantity)

2. Then run the crosstab query wizard on this base query, it will be self-explanatory.

I need to to get all the records of my tables customers and products, even the one without records in orders.

Then for the base query in 1. you need a Full Outer Join .

Answer 2

select idcustomer,[1]as p1,[2] as p2,[3] as p3,[4] as p4 from ( select idcustomer ,idproduct,quantity from Orders)ps pivot (sum(Quantity) For idproduct in ([1],[2],[3],[4]) ) pv; I am still not able to figure how to change the idcustomer to customername but this will help u(m sure for that!!).