Solved

Running Sum & Over Group - Problem

Posted on 2005-04-15
Medium Priority
381 Views
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.

David
0
Question by:kb1esx
• 2

LVL 38

Expert Comment

ID: 13792549
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.
0

Author Comment

ID: 13792605
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.

Thanks.

David
0

LVL 38

Accepted Solution

Jim P. earned 375 total points
ID: 13793387
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.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month16 days, 21 hours left to enroll