Multiple "value" in crosstab query

  • Thread starter Thread starter Matthew
  • Start date Start date
M

Matthew

I have a report generated from a crosstab query that I would like to use to
compare two "values" (side by side) for the same item.

What is the best way to do this?

Matthew
 
You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database.
-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
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.

There are some report examples at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
 
Back
Top