grouping in a report

[BB]

I am trying to create a report that shows the acct to be charged, the acct that receives the revenue, and the amount, so I'm dealing with AcctNumber, RevenueAcctNumber, and ChargedAmount. The user wants it broken down by RevenueAcctNumber(there only 2), by AcctNumber (all accts starting with different number should be grouped; for ex. all accts starting with 2 will be together, etc), and by pos or neg ChargedAmounts. I've tried grouping by RevenueAcctNumber, =[ChargedNumber]>0 (this probably isn't too good), and AcctNumber(using prefix characters). I think it actually works, including totals for each group. Is there a better way? Should it be made into 2 reports?
They also would like to have the total amount for each AcctNumber group, both pos and negs. Don't have any idea how to do this.
Thanks,
newbie

 

[Chris RR]

For the positive and negative amounts, I would add two new columns to your query, and I'm assuming that your report is based on a query, not a table.

The new fields would look something like this:

Pos_Charge:Iif([MyTable]![ChargedAmount]>0,[MyTable]![ChargedAmount],0)

Neg_Charge:Iif([MyTable]![ChargedAmount]<0,[MyTable]![ChargedAmount],0)

Then you can sum these up separately.