Allen Brown please - Crosstab

D

Duane Hookom

It's almost impossible to answer a crosstab related question without seeing
your SQL and knowing something about your data.
 
M

mhmaid

Thank you mr Duane Hookom for reply.
I am talking about the example shown on the link
http://allenbrowne.com/ser-67.html
which is using the sample database northwind to make a crosstab query.

this is the SQL I am trying to use.

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT Products.ProductID, Products.ProductName, Sum([Order
Details].Quantity) AS Total
FROM Products INNER JOIN ((Employees INNER JOIN Orders ON
Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY Products.ProductID, Products.ProductName
PIVOT Employees.LastName In ("Buchanan, Steven","Callahan, Laura","Davolio,
Nancy","Dodsworth, Anne","Fuller, Andrew","King, Robert","Leverling,
Janet","Peacock, Margaret","Suyama, Michael");

but unfortunately I am getting the wanted column headings with NO value.

as you can see , I have added the follwing :

"Buchanan, Steven", "Callahan, Laura", "Davolio, Nancy", "Dodsworth, Anne",
"Fuller, Andrew", "King, Robert", "Leverling, Janet", "Peacock, Margaret",
"Suyama, Michael"

to the column heading property of the query as instructed in the above link
by mr allen brown, and got the above sql.
 
D

Duane Hookom

The LastName field doesn't also contain first names. Your Column Headings
property needs to reflect values from the PIVOT field/expression.
I fixed two of these and left the remainder for you to fix:

PIVOT Employees.LastName In ("Buchanan","Callahan","Davolio,
Nancy","Dodsworth, Anne","Fuller, Andrew","King, Robert","Leverling,
Janet","Peacock, Margaret","Suyama, Michael");
 
Top