Help needed w/Crosstab Query and Column Values

[John Moore]

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

 

[Duane Hookom]

I don't care for the returned value from the Partition function. I prefer to
set up a table of ranges with a title:
tblGiftRanges
RangeMin Currency
RangeMax Currency
RangeTitle Text
You can add this table to your query and set a criteria under GiftAmt to
Between RangeMax and RangeMin
Then use RangeTitle as the column heading.

If you don't want to set up a table, either make sure the partition function
returns the "$" or use
PIVOT "Range To" & (GiftAmt+100)\100