sub-totals

[Bill Stanton]

I'd like to generate a report that displays the
name and total of a category and a grand-total
of all categories at the end of the report.
The RecordSource is of the form:

date; categoryID; categoryAmount

where categoryID is the ID of a normalized list
of names and categoryAmount is a currency
field. There is a One-To-Many relationship to
the RecordSource, so there can be many records
for each category.

E.g.,
catName1.................$total catName1
catName2.................$total catName2
catName3.................$total catName3
..
..
catNamen.................$total catNamen
---------------------------------
Total catName1-n $Total


Is this kind of thing done in the code-sheet of
the detail section? If so, does one code loops
in such code to add to sub-total accumulators
until the category changes (with the query
sorted by category)? My sense of Access
suggests the answer is NO!

Can anyone get me going on the right track?

Thanks,
Bill

 

[GVaught]

Access has a way to build grouping reports. Create a query based on the
table. Use this query to create a report through the Report wizard. As you
progress through the wizard you should see where you can set grouping and
then at the sorting window you should see a summary button. This summary
button will allow you to Sum, Avg, Min, Max and set percentages against the
data.

 

[Steve Schapel]

Bill,

Use the Sorting and Grouping facility in your report design to put in
a CategoryID Footer section (post back if you need more detailed help
with this), and in this section put an unbound textbox with its
controlsource set to...
=Sum([CategoryAmount])
and another textbox just the same in the Report Footer. Set the
Visible property of the Detail section to No. You will need to base
your report ona query which includes the Categories table so you have
access to the CategoryName field, and this can be put in the
CategoryID Footer section of your report.

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

Happy to offer further help if required, Bill.

- Steve Schapel, Microsoft Access MVP

 

[Bill Stanton]

Making progress...........

In the report footer, distinct from the category (group) footer,
I need to do a couple of arithmetic operations on a couple of
the "category sums". How do I reference them, either in code
or the report design? E.g., I need to capture the sum for two
of the categories separately from the "grand total".

Thanks,
Bill

 

[Steve Schapel]

Bill,

Try this...
=Sum([CategoryAmount]*Abs([CategoryID]=x))
or...
=Sum([CategoryAmount]*Abs([Category]="bla"))
.... where x is the numerical reference to the required category or
"bla" is the name of the category, and obviously substitute your own
actual field names in the place of CategoryAmount and CategoryID.
If this doesn't produce the desired result, please post back with some
more details and examples.

- Steve Schapel, Microsoft Access MVP

 

[Bill Stanton]

Steve,

Sorry to keep this thread hanging for so long. We're building a house
and I've been buried trying to keep ahead of the "train".

Using the expression of the form:
=Sum([CategoryAmount])-Sum([CategoryAmount]*Abs([CategoryID]=x))
did in fact give me the correct "CategoryAmount". I've pondered your use of
the ABS function, which obviously would always return a "1". However, it
remains a mystery to me as to why it works to make such a reference, unless
Access recognizes ahead of time that it needs that information in the
footer,
much like I assume it does for "Sum(Amount)"? (Care to explain?)

The only thing remaining is to create a couple of new categories, update the
data base with some test records and make reference to those new categories
in the footer section of the report I/we're working on. Nothing much
different
than what I was just successful with, so I think we're done.

Thanks again for your help.

Bill Stanton

Bill,

Try this...
=Sum([CategoryAmount])-Sum([CategoryAmount]*Abs([CategoryID]=x))
or...
=Sum([CategoryAmount]*Abs([Category]="bla"))
... where x is the numerical reference to the required category or
"bla" is the name of the category, and obviously substitute your own
actual field names in the place of CategoryAmount and CategoryID.
If this doesn't produce the desired result, please post back with some
more details and examples.

- Steve Schapel, Microsoft Access MVP

Making progress...........

In the report footer, distinct from the category (group) footer,
I need to do a couple of arithmetic operations on a couple of
the "category sums". How do I reference them, either in code
or the report design? E.g., I need to capture the sum for two
of the categories separately from the "grand total".

Thanks,
Bill

 

[Steve Schapel]

Bill,

Very happy to know that we have made progress.

As regards the Abs function in the expression, [CategoryID]=x will
either be true or false. Access assigns a value of 0 to false, and by
default a value of -1 for true. So I just use the Abs function to
turn the -1 into 1 so in the end CategoryAmount gets counted if
CategoryID is x and not counted if CategoryID is not x. It could have
equally well been expressed as...
=Sum([CategoryAmount])-Sum([CategoryAmount]*([CategoryID]=x)*-1)
.... or...
=Sum([CategoryAmount])+Sum([CategoryAmount]*([CategoryID]=x))

- Steve Schapel, Microsoft Access MVP

 

[Bill Stanton]

Steve,
I'm familiar with the ABS function, it was, as you wrote:
"CategoryAmount GETS COUNTED if CategoryID is x" that
wasn't clear.

Thanks again,
Bill

Bill,

Very happy to know that we have made progress.

As regards the Abs function in the expression, [CategoryID]=x will
either be true or false. Access assigns a value of 0 to false, and by
default a value of -1 for true. So I just use the Abs function to
turn the -1 into 1 so in the end CategoryAmount gets counted if
CategoryID is x and not counted if CategoryID is not x. It could have
equally well been expressed as...
=Sum([CategoryAmount])-Sum([CategoryAmount]*([CategoryID]=x)*-1)
... or...
=Sum([CategoryAmount])+Sum([CategoryAmount]*([CategoryID]=x))

- Steve Schapel, Microsoft Access MVP

Steve,

Sorry to keep this thread hanging for so long. We're building a house
and I've been buried trying to keep ahead of the "train".

Using the expression of the form:
=Sum([CategoryAmount])-Sum([CategoryAmount]*Abs([CategoryID]=x))
did in fact give me the correct "CategoryAmount". I've pondered your use of
the ABS function, which obviously would always return a "1". However, it
remains a mystery to me as to why it works to make such a reference, unless
Access recognizes ahead of time that it needs that information in the
footer,
much like I assume it does for "Sum(Amount)"? (Care to explain?)

The only thing remaining is to create a couple of new categories, update the
data base with some test records and make reference to those new categories
in the footer section of the report I/we're working on. Nothing much
different
than what I was just successful with, so I think we're done.

Thanks again for your help.

Bill Stanton