Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtotal for Top 20 (2003)

    I have a report that groups by Name, then Sales. I only want to show the Top 20 Sales clients for each Name, so I am using a counter that hides the Detail section when counter > 20. However, I want to subtotal the Top 20, but it always totals the sales for the name in the Name footer. I did use another counter and only show a running total in the detail when that counter = 20, but now each Name goes past one page and I only want to show one Name per page with the Top 20. Does anyone know of a way to subtotal the value for my top 20 per person in a footer? I am completely out of ideas. Thank you very much.

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

    Re: Subtotal for Top 20 (2003)

    Put a text box in the detail section with the following properties:
    Visible: False
    Control Source: the Sales field.
    Running Sum: Over Groups.
    Name: txtRunSum.

    Put a text box in the group footer for Name with the following properties:
    Visible: True.
    Control Source: empty (leave blank).
    Name: txtSum.

    Add the following code to the On Format event of the detail section:

    If counter = 20 Then
    Me.txtSum = Me.txtRunSum
    End If

    where counter is the name of the first counter you're using (you don't need the second one).

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal for Top 20 (2003)

    Thank you Hans for your suggestion. I see what you are doing and that makes sense. However, the txtSum continues to be the same number when my report goes to the next Name instead of resumming the new Name with their top 20. Is there a place in the On Format of the Group Footer that I should reset to zero, or is there a better way to clear this out? Thank you again for your assistance - it's tremendous what you do for everyone!

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

    Re: Subtotal for Top 20 (2003)

    Did you make sure to set the Running Sum property of the txtRunSum text box to Over Groups? That should cause the sum to reset to zero for each new Name.

  5. #5
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal for Top 20 (2003)

    Yes, I just doublechecked again and the detail txtRunSum is set to Over Group for running sum. I even made txtRunSum visible for now to see if it was recalcing on the new pages, and it is but the footer text box still remains the same. I must be making a simple error, because I see that it should work. Any other suggestions on where to look? Thanks,

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

    Re: Subtotal for Top 20 (2003)

    I have attached a working example, using tables derived from the Northwind sample database. Look at the way the text boxes are set up (I left all of them visible, so that you can see what happens), and at the code.

    If that doesn't help, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.
    Attached Files Attached Files

  7. #7
    Lounger
    Join Date
    Jan 2005
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtotal for Top 20 (2003)

    I figured out the problem. My 2nd page had less than 20 lines so it kept the prior running sum. I changed it to <= 20 and now it works. Thank you so very much.

Posting Permissions

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