Running Sum & Over Group - Problem

Posted on 2005-04-15
Last Modified: 2012-05-05
Hi All,

I've got a query that counts sales by day, and it groups the sales by <5K, >5K & Total sales for the day.  What i'm getting towards is a number of sales by month, YTD & Overall (yes, all sales in the table).

I'm using a report with groups on Year & Month.  

In the Month Footer, I have 2 sets of fields that sum TOTAL, 5K & 10K.  

The 1st set has Running Sum: Overall and gives me an accurate cummulative total of all sales.

The 2nd set has Running Sum: No and gives me an accurate cummulative total of sales within the Month.

In the YEAR Footer, I have fields that sum TOTAL, 5K & 10K. These fields have Running Sum: Over Group.  This function doesn't seem to work.

The values in the YEAR Footer are allways equal to the 'Running Sum: Overall' fields.  

Can anybody explain why this SIMPLE grouping isn't working?  I think I'm a little to close to the problem.

Any suggestions for a better process?  I want this to remain a report because I need to share these results.

Any help would be appreciated.

Question by:kb1esx
    LVL 38

    Expert Comment

    by:Jim P.
    In the YEAR Footer, I have fields that sum TOTAL, 5K & 10K. These fields have Running Sum: Over Group.  This function doesn't seem to work.

    You need to run the sum over the report not against the the group. It is attempting to total a totaled field. It needs to look at the raw field, not the subtotal.

    Author Comment

    I'm confused.

    I think your saying I need to change the setting of 'Running Sum' to No?

    I'll give it a shot.  I think I get it.  Since the Footer is resetting the total I don't need to use the Running Sum setting.  

    I think I get it.

    I'm going to have to play with it a bit.


    LVL 38

    Accepted Solution

    You want to set it up as a running sum of the original field, not of the monthly total.

    You have three footers: Monthly, Yearly and the Report

    The monthly accumaletes by what is in the group
    The Yearly has to relate to the totals for the yearly group, not a sum of the monthly.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now