Stefan,
I just answered a very similar problem. I think you can extrapolate the
solution to your own needs...
**Here's the poster's question. After studying the question, and the my
solution, you'll see that, except for the fields, your question, and the
concept is identical to yours.****
I have a report containing data on several different tax types. There are
a dozen or more possilble types of tax. However, there are two types that
make up the majority of the entries. How can I gather all of the lesser
tax
types under the "Other" group heading.
Something Like:
Franchise Tax
line 1
lines ...
Sales Tax
line 1
lines ...
Other Taxes
line 1
lines ...
One thought I had was to add another field to the table (or link one into
the query) for a separate tax sorting entry. franchise would = 1, sales
would =2, then all of the other tax types would =3.
****My Response******
Your idea is just fine!
However, you don't have to add a field to your table... you can do it "on
the fly"
(I'm assuming you have a field that identifies each tax as Franchise or
Sales or etc... something like a [TaxType]. If you don't, you probably
should.
In the query behind your report craete a calculated field in a column
like this... (assuming a numeric TaxType)
TaxGrp : IIF(TaxType = "Franchise Tax", 1, IIF(TaxType = "Sales Tax", 2,3))
Now group your report on TaxGrp, and use running sum Over Group.
Stefan******
As you can see the "Other" (the 3 value) can be used to create a sum of all
the other TaxRates except Franchise and Sales. Basically, your creating a
new "higher level" grouping that can add a group of groups, or just one...
according to the new grouping value. A sum for group 1, a sum for group 2,
and a sum for all other groups that have a 3.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Stefan said:
Is there a way to reset a running sum over all using VBA.
I cannot use the overgroup function because I need to retain the value
over
several groups however I do not want to retain it throughout the report.
I have it nested three deep
month
week
day
I tried to put me![runningsumfield]=0 in the group footer format section.
I get a cannot change the value of this field error.
