Help needed w/Crosstab Query and Col Values

[Tony Ciconte]

I am trying to get either the counts of or sum of data from a table
with gift amounts to display within certain ranges (e.g., $1-$99,
$100-$199, etc.) . The query is as follows:

TRANSFORM Sum(tblGifts.GiftAmt) AS [The Value]
SELECT tblGifts.MtypeID, Sum(tblGifts.GiftAmt) AS [Total Of GiftAmt]
FROM tblGifts
GROUP BY tblGifts.MtypeID
PIVOT Partition(Val([tblGifts].[GiftAmt]),0,1000,100) In ("$1 - $99",
"$100 - $199", "$200 - $299", "$300 - $399", "$400 - $ 499");

However, I get nothing displayed in any of the columns except the
SumOfGiftAmt column? I had hoped to have either a count or total in
each "$" column. However, no matter what I try, I cannot get it to
work.

Any and all help is appreciated.

TC

 

[Tom Wickerath]

Hi Tony,

Here is a similar example that works in the Northwind sample database. It
uses nested IIF statements to group ranges of data. You can likely use
similar logic in your crosstab query.

TRANSFORM Sum
(CCur((1-[Discount])*([Order Details.UnitPrice]*[Quantity]))) AS Sales
SELECT [Firstname] & " " & [LastName] AS Employee, Sum([Sales]) AS Total
FROM (Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID)
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY [Firstname] & " " & [LastName]
PIVOT IIf(Month([OrderDate]) Between 1 And 3,'Quarter 1',
(IIf(Month([OrderDate]) Between 4 And 6,'Quarter 2',
IIf(Month([OrderDate]) Between 7 And 9,'Quarter 3','Quarter 4'))));



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I am trying to get either the counts of or sum of data from a table
with gift amounts to display within certain ranges (e.g., $1-$99,
$100-$199, etc.) . The query is as follows:

TRANSFORM Sum(tblGifts.GiftAmt) AS [The Value]
SELECT tblGifts.MtypeID, Sum(tblGifts.GiftAmt) AS [Total Of GiftAmt]
FROM tblGifts
GROUP BY tblGifts.MtypeID
PIVOT Partition(Val([tblGifts].[GiftAmt]),0,1000,100) In ("$1 - $99",
"$100 - $199", "$200 - $299", "$300 - $399", "$400 - $ 499");

However, I get nothing displayed in any of the columns except the
SumOfGiftAmt column? I had hoped to have either a count or total in
each "$" column. However, no matter what I try, I cannot get it to
work.

Any and all help is appreciated.

TC