Report Grouping Criteria

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

 

[Duane Hookom]

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

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.

 

[Guest]

sorry for the multiple posts, but I kept getting the
message that my message was failing to post.

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

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?

.

 

[mfogarty]

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

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?

.

 

[Duane Hookom]

You can add a text box in your group footer
=DSum("[Balance]","

","[Balance] <1000 AND [Aging]=""" & [Aging] &
"""")

--
Duane Hookom
MS Access MVP

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

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?

.