Change field criteria in a report, or query

[mdnikki]

Sorry for the vague description but I have a query that I run that sums certain fields and gets a count of others. I am not sure if what I am trying to do now should be done in a report or the original query.

We are grouping giving levels by ranges.
Tier 1 - $1-99
Tier 2 $100-$99
Tier 3 - $500-$999
and on

However we don't want the final report to show Tier 1, etc. We would like it to show the gift range $1-99 instead. When I created the query to group these gifts I didn't use the giving levels because they will not sort in an ascending manner in the report so I named them Tier 1, Tier 2.



Now in the report though I need to see the giving level.

Here is the table that I am using

SumOf2011 AAA Paid	SumOfEOM Pledge Bal	SumOfEOM Pledge Amt	Expr1	CountOf2011 AAA Paid
$21,278.80	$978,623.40	$1,120,159.96	Tier 1	565
$216,889.40	$3,834,657.10	$4,501,240.00	Tier 2	1168
$84,920.00	$1,546,079.88	$1,754,010.00	Tier 3	155
$147,738.21	$1,785,039.17	$2,152,000.00	Tier 4	117
$46,800.00	$446,836.68	$570,000.00	Tier 5	18
$42,500.00	$535,931.75	$607,000.00	Tier 6	8
$92,500.00	$3,615,450.00	$4,293,450.00	Tier 7	6

And the query that creates this field tier used this criteria

Expr1: IIf([2011 AAA Paid]<=99,"Tier 1",IIf([2011 AAA Paid]<=499,"Tier 2",IIf([2011 AAA Paid]<=999,"Tier 3",IIf([2011 AAA Paid]<=2499,"Tier 4",IIf([2011 AAA Paid]<=4999,"Tier 5",IIf([2011 AAA Paid]<=9999,"Tier 6",IIf([2011 AAA Paid]>=10000,"Tier 7")))))))

Any help is appreciated.

[alansidman]

I would add an additional field using the same Expr you currently have but substitute the Tiers with the Dollars. Place it next to the current expression field. Check the box to display the field. Now in the earlier expression field, just uncheck the box to display the field.

Alan

[rpeare]

You already have your answer you just don't realize it . You had a couple of problems with your formula in that 99.99 would have been included in your 100-500 category which you should fix in your existing query but basically you add another field with the label you want then you can sort on your existing field and label it with the secondary field.

Expr2: IIf([2011 AAA Paid]< 100,"$1-$99",IIf([2011 AAA Paid]< 500,"$100-$499",IIf([2011 AAA Paid]<1000,"$500-$999",IIf([2011 AAA Paid]<2500,"$1000-$2499",IIf([2011 AAA Paid]<5000,"$2500-$4999",IIf([2011 AAA Paid]<10000,"$5000-$9999",IIf([2011 AAA Paid]>=10000,"$10000+")))))))

[mdnikki]

Thank you...this worked.