Re: Report Grouping Criteria

From: mfogarty (mfogarty5_at_yahoo.com)
Date: 07/21/04 

Date: Wed, 21 Jul 2004 10:21:16 -0700

The issue is that I want the total to appear in the group
footer, not the report footer and I want the total to
change based on the change in group.

Here is the example including the aging variable.

Table:

account balance aging(in days)
Bob $2,500 0-30
Joe $1,500 0-30
Mary $250 0-30
Jane $500 0-30
Matt $2,000 30-60
Mike $4,000 30-60
Laura $700 30-60
Paula $300 30-60

Report:

Group 1 Accounts 0-30 Days

Bob $2,500
Joe $1,500

Total for accounts greater than $1,000 and 0-30 days
$4,000(Bob + Joe)
Total for accounts less than $1,000 and 0-30 days
$750(Mary + Jane)

Group 2 Accounts 30-60 Days

Matt $2,000
Mike $4,000

Total for accounts greater than $1,000 and 30-60 days
$6,000(Matt + Mike)
Total for accounts less than $1,000 and 30-60 days
$1,000(Laura + Paula)

Users get to see the detail for those >$1,000, but they
get a total for all those <$1,000 at EACH group.

>-----Original Message-----
>You keep talking about a field named Aging but I don't
see one in your
>sample records. You could filter out the records with a
balance less than
>1,000 and then add a summary of these as a subreport in
the report footer.
>You can then add the total from the subreport to the
total from the main
>report to get a total of all accounts.
>
>I suppose you could also skip the subreport and just use
a text box with a
>control source like:
>=DSum("Balance","Table","Balance <1000")
>--
>Duane Hookom
>MS Access MVP
>
>
>"mfogarty" <mfogarty@discussions.microsoft.com> wrote in
message
>news:70BD7793-18A1-4DE4-BAD6-776210F2E5BB@microsoft.com...
>>
>> I have a table containing a list of records for which
users want to see a
>report. They want the report grouped by the field
named "aging"(ex:
>0-30,30-60,60-90, etc.) and only want to see the detail
for accounts with a
>"balance" greater than $1,000. At the bottom of
each "aging" grouping they
>want to see a sum of the "balance" field. However, they
also want to see a
>total "balance" for accounts with a "balance" less than
$1,000.
>>
>> In other words they want detail only for accounts with
a "balance" greater
>than $1,000m, but want two total lines: one for those
accounts greater than
>$1,000 and one for those accounts <$1,000.
>>
>> The following is a brief example:
>>
>> Table:
>>
>> Bob $2,500
>> Joe $1,500
>> Mary $250
>> Jane $500
>>
>> Report:
>>
>> Bob $2,500
>> Joe $1,500
>>
>> Total for accounts greater than $1,000 $4,000(Bob +
Joe)
>> Total for accounts less than $1,000 $750(Mary +
Jane)
>> Total for all accounts
$4,750
>>
>> I guess there are two ways to solve this problem.
>>
>> A. Base the report on a query that inlcudes all records
and try to figure
>out how to exclude those accounts with a balance <$1,000
in the detail
>section.
>> B. Base the report on a query that excludes records
with a balance <$1,000
>and create a control in the group footer to calculate the
total for records
>with a balance less than $1,000
>>
>> I chose the latter approach and set the criteria of
the "aging" field in
>the query of the group footer's control to [aging]. I
used [aging] because
>when I created a text box in the "aging" grouping header
set equal to
>[aging] Access knew to change the grouping header at each
change in "aging."
>When I set the criteria of the "aging" field in the
control of the group
>footer to [aging], however, it repeated the first record
over and over. In
>other words, the criteria didn't change at each change in
grouping like the
>text box did.
>>
>> I did something very similar to this in a form, but the
Event property
>that I used in the form is unavailable in the grouping
section of the
>report.
>>
>> Any ideas?
>>
>
>
>.
>

Relevant Pages

  • OT - Bush & Rice Should Also Be Offed
    ... Syria Must Be Held Accountable for Hariri Killing, ... Rice spoke to reporters after the release of a report by U.N. ... with horror'' watching televised accounts of the brutality experienced ...
    (alt.sports.basketball.nba.la-lakers)
  • Re: Report Grouping Criteria
    ... not the report footer and I want the total to ... Here is the example including the aging variable. ... account balance aging ... Group 1 Accounts 0-30 Days ...
    (microsoft.public.access.reports)
  • Multiple Column Report - esp for Duane Hookom
    ... FIRST WATER DISTRICT ... Then a page break and the Second Water District, ... My table that holds the actual report data is structured: ... AcctNo determines the order in which accounts are printed on the IS. ...
    (microsoft.public.access.reports)
  • Criteria in Report Group Control
    ... Now in your report combine the query for the ... At the bottom of each "aging" ... grouping they want to see a sum of the "balance" field. ... accounts with a "balance" less than $1,000. ...
    (microsoft.public.access.reports)
  • Re: Opening report from form
    ... Use the WhereCondition of the OpenReport action to limit the report to the ... > contains two subforms, Accounts subform and Entries ... > Disbursements form via a matter id number and Entries ...
    (microsoft.public.access.forms)