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