Hook'D on Access

Multiple Value
Crosstab Query

• Create a new table tblXtabColumns with a single field [FldName]
• add two records to this table
"Quantity" "QtyXPrice"
• create a crosstab with the table [Products], [Orders], [Order Details], and [tblXtabColumns]
• join the first three tables as expected but don't join tblXtabColumns to any other table
• your Column Heading expression is
  Expr1:[FldName] & Month([OrderDate])
• your Value expression is
  DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order Details]![UnitPrice])
  Sum
• I set OrderDate for the first three months of 1998

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);