Multiple "value" in crosstab query

[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

 

[Herbert Chan]

Create an additional query to place those values side by side yourself.

herbert

 

[Duane Hookom]

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