Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals in a report (Access 97/Windows 98)

    In our church-giving relational database a number is allocated to a person when they make their first donation. Over the year they could make any number of donations. So we have one personal number linked to many entries.

    I am trying to print a report of what has been given and under what category. Now the amounts and categories are straightforward. A report is linked to a query that has the Personal Number, the amount ant the category and the date. (The date is there to select the tax year.)

    All I want is the totals, so in the report, I simply sum them in the category footer and not bother about anything in the detail section. If I then use count(*), in the category footer, I get the total number of entries for that category and as there are about 10 categories, I end up with a simple 10 line report.

    The trouble is it

  2. #2
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals in a report (Access 97/Windows 98)

    =Sum(Iif([SomeField]>0,1,0)) where somefield is the field containing the amount

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Totals in a report (Access 97/Windows 98)

    Do you want the number of people who gave for each category, or do you simply want the number of people who gave during the current year? You can determine the latter by doing a select query on just the people number where the year is the current year (but don't return it in the query, and turn on the Unique Values property for the query. Then turn on the query totals option from the menu, and tell it to count the person number field. That will give you the total. It isn't as simple to get it to the report, but it will at least give you the count.

    If you want counts by category, you have to use a similar approach where you sum each category for each person, and then do a second query based on the sum query which counts by group and also sums the totals for each category. That could actually be plugged into the report instead ofusing sums in the footers. Hope this helps - church software has some interesting twists - I build a fairly complex system for mine 3 years ago and they still keep thinking up new things they want.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals in a report (Access 97/Windows 98)

    Many thanks for both your replies - much food for thought - I have been snowed under with work, so have not got round to doing any more work on it.

    But just wanted to say your helpfull replies were most appreciated

    Michael Peak

Posting Permissions

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