How to manually arrange records and total them

[John]

I have a query called "qry_Customer_Balances_Grouped". The following are the
records:

Business_Group_ID Business_Group SumOfAvg_Book_Bal
6 Los Angeles 106,929,070.00
4 Seattle 327,618.00
20 California Corporate Trust 3,745,013.00
11 Corporate Funds 13,643,427.00

What I am trying to do is place the first two records exactly as they appear
above and total them on "SumOfAvg_Book_Bal". Then I want to do the same for
the last two records. In other words I need to manually place the records in
any order I want on the report and total them in separate groups. Any ideas?

 

[Al Camp]

John,
The first question would be... why do you need to separate the first two from the last
two? If there is some logical reason why, then you should have an additional field in
your table that indicates that logic...
BusGroup ReportID
Los Angeles 1
Seattle 1
CalCorpTrust 2
CorpFunds 2
(much like if LA and Seattle were in a different "region" from CalCorp and CorpFunds...for
example)

Now you can group on the 1 and 2 and total accordingly.

 

[John]

I was thinking of creating four text boxes in the report header and doing an
if statement on the "business_group_id" to find the value, and then bring in
the corresponding "Business_Group" description then somehow bring in the
values using a similar procedure. Haven't gotten the first step to work
though.

 

[Al Camp]

You could add a field to your query...
BusGrpBreak : IIF(BusGrp = "Los Angeles" or BusGroup = "Seattle",1,2)
then... group/break on BusGrpBreak on the report.

But, what happens when you add another BusGrp? That query will have to be recoded for
the new "Break" value. If you do what I originally suggested, at the table level, the
table is all you would have to update. You could even add a 3 group in the future etc..