Criteria in Report Group Control

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Hi.
The way I would approach this is to create two separate
queries for the summaries, each filtered according to your
specs. Now in your report combine the query for the
report you already have with the two new queries now you
have the data available for the report as specified.
An other option is, in a textbox enter
=Sum([YourTotal] > 1000)
or
=Sum([YourTotal] < 1000)
or
=Sum([YourTotal])
as applicable for the specific requirement.
Hope this helps.
Fons
-----Original Message-----

I have a table containing a list of records for which
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Event property that I used in the form is unavailable in
the grouping section of the report.
 
Back
Top