Apply the procedure below. If you need help setting this up, I can help you
for a very reasonable fee
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Create A Crosstab Query With More Than One Value
Note: You can create multiple values by combining a crosstab and cartesian
query. The following is an example using Northwind. The example creates a
crosstab query showing both a Quantity and Quantity * Price value for each
product.
1.. Create a new table TblXtabColumns with a single field [FldName].
2.. Add two records to TblXtabColumns, "Quantity" and "QtyXPrice"
3.. Create a crosstab query with the table [Products], [Orders], [Order
Details], and
[tblXtabColumns].
1.. Join Orders and Order Details on OrderID
2.. Join Products and Order Details on ProductID
3.. Do not join tblXtabColumns to any other table
1.. Pull down ProductName from Products. Set its Total property to Group
By and set its Crosstab property to Row Heading
2.. Enter the following expression in the second query field:
Expr1:[FldName] & Month([OrderDate])
1.. Set its Total property to Group By and set its Crosstab property to
Column Heading
1.. Enter the following expression in the third query field:
DaVal: Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details].[UnitPrice]))
1.. Set its Total property to Expression and set its Crosstab property to
Value
1.. Pull down OrderDate from Orderss. Set its Total property to Where and
leave its Crosstab property blank.
1.. Set the criteria for OrderDate to:
Between #8/1/1994# And #9/30/1994#
The full SQL is:
TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details].[UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Products INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Orders.OrderDate) Between #8/1/1994# And #8/31/1994#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);
The crosstab query returns four columns per Product:
Where "8" and "9" are the months August and September.