How to group by giving levels

[mdnikki]

I am trying to group records in a query by their giving level.
In my table I have a list of gifts. I need to first group these by giving levels $1-$99, $100-$499 etc. Then I need to get a count of how many are in each of these groups.

So my table looks like this
2011 gift
$250
$100
$99
$50
$250
$95


$252

The end result is I need to group them by giving levels as noted above so I just see there were 3 gifts under $100, 4 gifts $100-$499 etc.

Any help is appreciated.

[rpeare]

if you're doing this in a query do something like:

iif([gift] <=99, "Level 1 Gift", iif([gift] <= 499, "Level 2 Gift", "Unidentified gift"))

[mdnikki]

Thank you..that worked to group them by giving levels. Then what I need to do is get a count of how many are in each level.

[rpeare]

use an aggregate query and put in the 'gift' field and instead of using sum, use 'count' as your aggregate.

[mdnikki]

Thank you...first part accomplished..now I have this. And I need to sum how much was given at each level. So the Giving level is the new field created by the formula used above. But I need to sum. In the giving level $1000-2499 this much came in for 2011 AAA Paid, this much for EOM Pledge Balance and this much for EOM Pledge amount. Is this possible.

2011 AAA Paid	EOM Pledge Bal	EOM Pledge Amt	Giving Level
$1,000.00	$5,700.00	$6,000.00	$1000-2499
$1,250.00	$8,166.63	$10,000.00	$1000-2499
$1,000.00	$6,000.00	$7,500.00	$1000-2499
$1,000.00	$5,000.00	$15,000.00	$1000-2499
$1,250.00	$40,000.00	$50,000.00	$1000-2499
$1,000.00	$4,633.34	$5,000.00	$1000-2499
$1,000.00	$1,000.00	$1,500.00	$1000-2499
$1,250.00	$13,000.00	$15,000.00	$1000-2499
$1,000.00	$3,700.00	$5,000.00	$1000-2499
$1,108.21	$8,000.00	$10,000.00	$1000-2499
$1,750.00	$15,000.00	$30,000.00	$1000-2499
$1,000.00	$2,600.00	$3,000.00	$1000-2499
$1,000.00	$3,875.00	$5,000.00	$1000-2499
$1,500.00	$8,000.00	$10,000.00	$1000-2499
$2,000.00	$20,000.00	$25,000.00	$1000-2499
$1,000.00	$8,000.00	$10,000.00	$1000-2499
$1,000.00	$2,000.00	$3,000.00	$1000-2499
$2,000.00	$2,000.00	$5,000.00	$1000-2499
$1,750.00	$17,000.00	$20,000.00	$1000-2499

[rpeare]

use SUM on all your amount fields and GROUP BY on your GIVING LEVEL label field.

[mdnikki]

You are a life saver! Thank you..worked perfectly!

[mdnikki]

Ok I am back. So now when I look at my report if there are no gifts in a particular level the level itself won't appear on the report. Here is the critiria used to group the gifts in the levels in the query.

Giving Level: IIf([2009 AAA Paid]<=99,"$1-$99",IIf([2009 AAA Paid]<=499,"$100-$499",IIf([2009 AAA Paid]<=999,"$500-$999",IIf([2009 AAA Paid]<=2499,"$1,000-$2,499",IIf([2009 AAA Paid]<=4999,"$2,500-$4,999",IIf([2009 AAA Paid]<=9999,"$5,000-$9,999",IIf([2009 AAA Paid]>=10000,"$10K+")))))))

But in this case there are no $10K+ gifts so my report displays this, the 10K category is missing. I would rather it display with 0 gifts, $0. Is this possible.

Giving Level	CountOf2009 AAA Paid	SumOf2009 AAA Paid	SumOfEOM Pledge Amt	SumOfEOM Pledge Bal
$1-$99	160	$5,161.50	$1,166,224.56	$1,066,561.38
$100-$499	117	$18,525.00	$466,911.00	$418,720.07
$500-$999	15	$8,300.00	$206,200.00	$183,450.01
$1,000-$2,499	7	$8,000.00	$57,200.00	$46,100.00
$2,500-$4,999	2	$6,500.00	$115,000.00	$112,000.00
$5,000-$9,999	2	$11,525.00	$637,500.00	$429,000.00

[rpeare]

To do that you would have to have a table with the gift levels on it which could get tricky if your gift levels change over time you would have to modify the 'levels' table every time your criteria for gift ranges changed. Your query driving what you currently have would also have to change quite a bit to handle looking up acceptable ranges from your 'levels' table.

if you pursue this you'd need a table like:

tblLevels
Lev_ID  Lev_Lower  Lev_Upper  Lev_Label  Lev_Description  Lev_Order
1       0          99.99      $1 - $99   Copper           1
2       100        499.99     $100-$499  Bronze           2
3       500        999.99     $500-$1000 Iron             3
etc.