Report Grouping...Help!

[Devon B]

I need some help in creating this report smartly by, the way be a little
patient with me I am not an IT guru.
I need to create a report that shows account and account balances in strata
by product type. The report should show account that falls within the
following balances:
< $500

=501 and <=1000
=1001 and <=1500 etc until $10,000

Then $5000 increment until $50,000 then $25000 increments from there.

The way I am thinking of setting up this report is to create a calculated
field for account balance increments (using iif statement). Then I’ll group
the report on product code and the calculated field. I am not sure I am
going about this the right (efficient/smart) way. Can I get some help with
how to setup this report?

Thanks in advance for any help or suggestion you provide.

Devon B-

 

[Duane Hookom]

I would never attempt to nest more than 2 IIf()s. IMHO the most robust
solution would be to create a table of Balance Ranges
tblBalanceRanges
================
MinBalance
MaxBalance
BalanceRangeName

You would add records in this table to manage your ranges in data rather
than in a complex expression.

You would need to have the account balance available in the report's record
source query. Add the range table and set the criteria under the account
balance field to:
Between MinBalance and MaxBalance
Add the fields from the range table to the grid so you can use them for
sorting and grouping.

An alternative method would be to create a user-defined function thata
accepts the balance as the argument and returns a range value.