Allen Brown please - Crosstab

  • Thread starter Thread starter Guest
  • Start date Start date
It's almost impossible to answer a crosstab related question without seeing
your SQL and knowing something about your data.
 
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.
 
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");
 
Back
Top