Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Sum (2000)

    1. I have a report that breaks on Client Number, Client Manager, District.
    2. I would like to count the Clients for Client Manager, District and a grand total clients for Report.
    3. I need to use a running sum but it only gives me a count for the whole report.
    4. How would I count the clients for the Client Manager and the District?
    5. In the Client Number header I put an unbound text box and put its control source =1and name it Clients.
    6. The I put "running sum over All"
    7. Then in the Report footer I put an unbound text box and =[Clients] in the Control Source.
    8. I don't know what to do for Client Manager footer or the District footer. Any help is greatly appreciated.

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

    Re: Running Sum (2000)

    Put a text box in the Client Manager header (or footer).
    Set its Control Source to =Count(*)
    Set its Running sum propertiy to Over All
    Do the same for the District header (or footer)
    Do the same for the Report footer (here, it doesn't matter what you put in the Running Sum property)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum (2000)

    That didn't work. Maybe my explanation wasn't clear as far as using Client instead of Client Number which is one of the report breaks. Since I am trying to count Client Numbers, wouldn't I have to put something in the Client Number header? I see what it is doing - it is counting the group numbers which is another field under clients. Instead of counting the Client Number it counts the number of Group Numbers. If there are 2 Clients and each have 8 groups, I get 16 instead of 2.

    1. I have a report that breaks on Client Number, Client Manager, District.
    2. I would like to count the Client Numbers for Client Manager, District and a grand total of client Numbers for Report.
    3. I need to use a running sum but it only gives me a count for the whole report.
    4. How would I count the client Numbers for the Client Manager and the District?
    5. In the Client Number header I put an unbound text box and put its control source =1and name it Clients.
    6. The I put "running sum over All"
    7. Then in the Report footer I put an unbound text box and =[Clients] in the Control Source.
    8. I don't know what to do for Client Manager footer or the District footer. Any help is greatly appreciated.

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

    Re: Running Sum (2000)

    What is the exact order of the grouping levels in the Sorting and Grouping window?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum (2000)

    District, Client Manager, Client Number, Group Number

    I got it to work for counting for the report and counting for the Client Manager but not for District.
    I put an unbound text box in the Client Number header. Put its control source =1 and name CL. Put Running count over all. I put another textbox in the report footer and put its control source = CL. Works fine.

    I put another unbound text box in the Client Number header. Put its control source =1 and name CL2. Put running count over group. I put a text box in the Client Manager footer and put its control source = CL2. Works fine.

    Then I put another unbound text box in the Client Number header. Put its control source =1 and name CL3. Put running count over group. I put a text box in the District footer and put its control source = CL3. It doesn't work. Instead of counting for District, it is counting for Client Manager again.

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

    Re: Running Sum (2000)

    Sorry, I couldn't get into the Lounge for almost an hour. In the meantime, I worked out a solution, which turns out to be more complicated than I first expected. Here it is, including the parts you already solved yourself:

    I'm not quite sure you really mean a running sum, but there should be something you can use in the following. Make a copy of the report, or of the entire database before starting.
    1. <LI>Put two text boxes in the group footer for Client Number. They will be hidden later on.
      <LI>Set the Control Source for both text boxes to =1.
      <LI>Set the Running Sum property for the first one to Over Groups. Name this one txtCountGroup.
      <LI>Set the Running Sum property for the second one to Over All. Name this one txtCountAll.

      <LI>Put two text boxes in the group footer for Client Manager.
      <LI>Set the Running Sum property for both text boxes to No.
      <LI>Set the Control Source for the first one to =[txtCountGroup]. This one displays the number of Client Numbers for the current Client Manager.
      <LI>Set the Control Source for the second one to [=txtCountAll]. This one displays the cumulative number of Client Numbers.

      <LI>Put three text boxes in the group footer for District.
      <LI>Set the Running Sum property for all three text boxes to No.
      <LI>Set the Control Source for the first one to =[txtCountAll]. This one displays the overall cumulative number of Client Numbers.
      <LI>Leave the Control Source for the second one blank. Name this one txtPreviousCount. It will be hidden later on.
      <LI>Set the Control Source for the third one to =[txtCountAll]-[txtPreviousCount]+1. This one displays the cumulative number of Client Numbers for the current District.
      <LI>Put one text box in the Report Footer.
      <LI>Set its Control Source to =[txtCountAll]. This one displays the overall number of Client Numbers.

      <LI>If Group Header for District is set to No, you must set it to Yes, but you can set the height of the group header to 0.
      <LI>Click on the group header for District.
      <LI>Click in the On Format event in the Event tab of the Properties window.
      <LI>Select Event Procedure from the dropdown list
      <LI>Click the ... to the right of the dropdown arrow.
      <LI>Insert the following code:

      If Not fRepeat Then
      fRepeat = True
      Me.txtPreviousCount= Me.txtCountAll
      End If

      <LI>At the top of the module, below Option Compare Database and Option Explicit (if present), insert:

      Private fRepeat As Boolean

      <LI>Switch back to Access.
      <LI>Click on the group footer for District.
      <LI>Click in the On Format event in the Event tab of the Properties window.
      <LI>Select Event Procedure from the dropdown list
      <LI>Click the ... to the right of the dropdown arrow.
      <LI>Insert the following code:

      fRepeat = False

      <LI>Switch back to Access.
      <LI>Save the report.
    After testing, set the Visible property for the text boxes you don't want to see to No.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Sum (2000)

    Thank you so much - it works perfectly. It amazes me how you can decipher what I mean. As usual, I am forever in your debt.

Posting Permissions

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