Totalling Groups

[David Whitaker]

I have got a report that groups by a textbox value(1,2,and 3). I do have a
group footer that has two textboxes that sums each group, so far so good.
How can you bring each groups total down to the report footer or is that not
the appropriate place to do that? What I am trying to do is to have at the
bottom of the report, totals from each group grouped together as a summary.
Hope I don't confuse someone
example
(not like this)
12
12
10
total 34
10
11
12
total 33
10
10
total 20
[bottom of page]

(like this)
12
12
10
total 34
10
11
12
total 33
10
10
total 20

total 34
total 33
total 20
[bottom of page]

 

[Eric Butts [MSFT]]

Hi David,

The only way I see doing something like this is using Domain Aggregate
functions. Now Domain Aggregate functions are solutions you would like to
stay from because of the overhead involved. Anyhow, here's an example of
the control sources you would have for the textbox controls on your Report

Textbox1: =DSum("[field amount]","[name of the table]","[field grouped
on] = abc")
Textbox2: =DSum("[field amount]","[name of the table]","[field grouped
on] = def")
Textbox3: =DSum("[field amount]","[name of the table]","[field grouped
on] = ghi")


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights




--------------------
| From: "David Whitaker" <davidw@(removethistext)mcintoshconstruction.net>
| Subject: Totalling Groups
| Date: Mon, 10 May 2004 16:36:16 -0500
| Lines: 40
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.reports
| NNTP-Posting-Host: cookeville-68-112-64-8.midtn.chartertn.net 68.112.64.8
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
..phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.reports:137894
| X-Tomcat-NG: microsoft.public.access.reports
|
| I have got a report that groups by a textbox value(1,2,and 3). I do have a
| group footer that has two textboxes that sums each group, so far so good.
| How can you bring each groups total down to the report footer or is that
not
| the appropriate place to do that? What I am trying to do is to have at the
| bottom of the report, totals from each group grouped together as a
summary.
| Hope I don't confuse someone
| example
| (not like this)
| 12
| 12
| 10
| total 34
| 10
| 11
| 12
| total 33
| 10
| 10
| total 20
| [bottom of page]
|
| (like this)
| 12
| 12
| 10
| total 34
| 10
| 11
| 12
| total 33
| 10
| 10
| total 20
|
| total 34
| total 33
| total 20
| [bottom of page]
|
|
|

 

[David Whitaker]

Textbox1: =DSum("[field amount]","[name of the table]","[field grouped
on] = abc")

this is a something I havent seen yet, here are my resources, can you help
me with making sure I understand what you are doing in your statement

tablename [usage]
in report detail is:
textbox1's controlsource is [gallons]
textbox2's controlsource is [cost]
grouped by [type]

in types footer is:
textbox3's controlsource is; =sum(nz([gallons],0))
textbox4's controlsource is; =sum(nz([cost],0))

here is my attempt in the report footer
Textbox5: =DSum("[gallons]","[usage]","[type] = 1")
Did I fail?

Last Question?
What did you me by the overhead involved?
Thanks for helping me learn!
David

 

[Duane Hookom]

I think using a domain aggregate function wastes resources and will
potentially give inaccurate results. For instance, if you open the report
with a where clause (filter), the DSum() table will not also have the same
filter applied.

I would either create a totals query and use it as the record source for a
subreport to place in your reports footer. If you add another group, it will
automatically appear in the subreport without having to modify the report.

Another solution is to use expression like:

=Sum(Abs([TYPE] = 1) * [field amount])
This assumes Type is a numeric field.
--
Duane Hookom
MS Access MVP

Textbox1: =DSum("[field amount]","[name of the table]","[field grouped
on] = abc")

this is a something I havent seen yet, here are my resources, can you help
me with making sure I understand what you are doing in your statement

tablename [usage]
in report detail is:
textbox1's controlsource is [gallons]
textbox2's controlsource is [cost]
grouped by [type]

in types footer is:
textbox3's controlsource is; =sum(nz([gallons],0))
textbox4's controlsource is; =sum(nz([cost],0))

here is my attempt in the report footer
Textbox5: =DSum("[gallons]","[usage]","[type] = 1")
Did I fail?

Last Question?
What did you me by the overhead involved?
Thanks for helping me learn!
David