Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Report Summary Formula (XP)

    I teach workshops for businesses. Workshop costs vary by topic and there is a minimum of 3 attendees per workshop. Where fewer than 3 people sign up and the workshop is held anyway, the company is charged for the minimum number.

    My report lists attendees / workshop. The records are grouped on Workshop topic. In the group footer are 2 textboxes with formulas. Text22 has the formula = count([Lastname]) and Text26 has IIf([text22]<3,3*[cost],Sum([COST]))
    for a cost per workshop.

    I want a grand total in my report footer and the most straightforward method would be to sum Text26, but that seems impossible. And I do not know how to build a formula that tests for fewer than 3 line items within a group.

    This must be a common need. Is there a simple solution?

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

    Re: Report Summary Formula (XP)

    You can only sum fields and expressions, not text boxes, in a report.

    Create a duplicate of Text26 in the group footer, and set its Running Sum property to Over Groups (or Over All, it doesn't really matter here). Set its Visible property to No (the text box may even be on top of other controls, since you won't see it anyway.) Let's say that you name this text box txtRunningSum (I always prefer to give controls meaningful names; if I have to edit the report in a year or so, Text26 doesn't give me a clue what it is intended for.)

    Set the Control Source of the grand total text box in the report footer to =[txtRunningSum]

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Summary Formula (XP)

    I'm still missing something. If I copy the formula from the group footer to the report footer, and set running total to either Over Groups or Over All, it does sum the actual people who attended, but it ignores the fact that one workshop needs to be counted as 3, even though only 1 attended. The result is the same as =sum([Cost]) would be. I need to be able to refer to an individual group in my report footer so I can build an If statement, I think.

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

    Re: Report Summary Formula (XP)

    Apparently I wasn't clear enough. You should duplicate the text box with the =IIf(...) expression within the group footer, and leave it there, don't put it in the report footer. Set the Running Sum property for this text box to Over Groups. Put another text box in the report footer, and set its control source to refer to the text box with the running sum in the group footer.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report Summary Formula (XP)

    Oops! My apologies. You said it right; I read it wrong. Yes, it works. I need to think about this one, because how it is working isn't immediately apparent, but it certainly IS working.

    Thanks.

Posting Permissions

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