sub-totals

  • Thread starter Thread starter Bill Stanton
  • Start date Start date
B

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
 
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.
 
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
 
Happy to offer further help if required, Bill.

- 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
 
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
 
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




Steve Schapel said:
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
Click to expand...
Click to expand...
 
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,
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


Steve Schapel said:
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
Click to expand...
Click to expand...
 
Back
Top