Hook'D on Access


Multiple Value
Crosstab Query

You can create multiple values by combining a crosstab and cartesian query.
Create a sample using the Northwind database to create both a Quantity and Quantity * Price value for each column.
  • 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])
  • I set OrderDate for the first three months of 1998
The full SQL is:
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]);
You will get two columns per month QtyXPriceN and QuantityN.
| Contact | ©2007 Duane Hookom