Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    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. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    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
    Address ...

    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. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Francois

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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)
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    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
  •