# Thread: Group Intervals (2003 SP3)

1. ## Group Intervals (2003 SP3)

I have a report that lists my organization's contributors, based on their total contributions. The report uses a Totals query that calculates the sum of all their contributions. Setting the Report's Sorting and Grouping Field to SumofContributions (from the query) I get too many small groups, i.e. \$100, \$150, \$275, etc. I want to display the report group totals in ranges, such as \$100-\$500, \$501-\$1000, etc. How do I define the ranges? In the query or in the report?

2. ## Re: Group Intervals (2003 SP3)

In attachment a sample.
First you create a table with 3 fields. The first field is a continuous numbers used to sort. The second field is the maximum of the ranges you want to use. The third field is just text to use as Groupheader text in the report.
With query qrySorting I use the function Sort (in the module BasSorting) to retrieve the sortnumber for each gift.
In the query qrySortingWithText I link the qrySorting to the table Sortorder to retrieve the sorttext.
With this query you can build a report, sorted on the sortnumber and have text to put in the group heading.

HTH

3. ## Re: Group Intervals (2003 SP3)

Right on the money, so to speak! Thanks for your simple, elegant solution. I'll redesign the queries with my field names and test it. I'll then report back.

4. ## Re: Group Intervals (2003 SP3)

I'm having a little trouble generating the report. Your solution does not link the sortnumber with the calculated SumofContributions from my query. The query I was using for the report has two tables:

tblContacts...........tblContributions
ContactID..............ContactID
LastName.............Contribution
FirstName.............ContributionDate

They are joined on ContactID in a one-to-many relation. When I turn the query into a Totals query I sum on Contribution, producing the output field SumofContributions. That's what goes into the report alongside the Contributor's name. How do I attach a sortnumber to a particular contributor? I have tried making qrySortingwithText a Totals query, but that does nor work.

5. ## Re: Group Intervals (2003 SP3)

>When I turn the query into a Totals query I sum on Contribution, producing the output field SumofContributions.
That Totals query should be use as base for the query qrySorting.

If you want you can attach a sample of what you already have so I can have a look at it. See <post:=401,925>post 401,925</post:> for attaching a db to a post.

6. ## Re: Group Intervals (2003 SP3)

Happy Holidays, Francois. Sorry I didn't answer sooner but I was away for a few days.
I tried your last suggestion but the query failed. It said that I had circular references, which I could not find. I have attached a sample. I am confidant that you can "sort" it out.

7. ## Re: Group Intervals (2003 SP3)

See if the attached version does what you want.
(Warning: I removed database objects that weren't relevant to the problem)

8. ## Re: Group Intervals (2003 SP3)

That's what I want. Thanks, Hans.
Happy Holidays,

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•