cross tab queries with multiple values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a query/report that shows multiple calculations within each
month of the year. I can do this by creating multiple cross tab queries and
then joining them together into one, but I was wondering if there is a
better/quicker way?
 
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])
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.
 
Duane,

Thank you for your response, it works well. I am having some difficulty
getting in over two values though. Here is my SQL code:

TRANSFORM Sum(IIf([totals]="totals",[257084 Actuals],[257084 Reversals])) AS
DaVal
SELECT qryELDSum257084_ProcessingTBExpress.[Account #]
FROM tblTotals, qryELDSum257084_ProcessingTBExpress
GROUP BY qryELDSum257084_ProcessingTBExpress.[Account #]
PIVOT [totals] & Format([FirstOfGL-DATE]," mmm");

Here is my Field row entry:
DaVal: Sum(IIf([totals]="totals",[257084 Actuals],[257084 Reversals]))

When I try to add in additional values it tells me I have the wrong number
of arguments. Please advise.

Thanks again.
 
Back
Top