By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

RunningSum options: Query or Report?

P: n/a
I'll preface this question with my assertion that I'm not
intellectually challenged...(if you ask my wife on a good day she'll
probably agree with you too)

However, this problem is making me feel that way.

I have some data in a QUERY. The columns of interest are:

INTERNALID, NUMBER, etc....
A, 100
A, 120
A, 140
B, 220
B, 210
C, 133
D, 853
D, 233

I want to sum the NUMBER column for each INTERNALID (A, B, C, etc.)
such that this data can be put on a report. Resultant set would be
something like:
A, 360
B, 430
C, 133
D, 1086
OPTION 1: RunningSum in a query?
- I've read the articles in this newsgroup that say this is both poor
practice and can be difficult. I tried this anyway by inserting a
calculated field into the query and had trouble with the format:

PBV: DSum([NUMBER], "tblName", "[INTERNALID] = " & [INTERNALID])

I know the problem is the format of the 3rd argument but don't know
how to correct it.

OPTION 2: Use RunningSum property on the report.
- I don't want a simple running sum, I want a running sum only for
certain conditions (for when INTERNALID = A, B, C, etc). I'm not
having much success formatting this property successfully on the
report.
Which route should I use (option 1 or option 2)? Any code examples
appreciated for the given case...

Thanks - Eric
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Eric Schneider" <es**********@hotmail.com> wrote in message
news:66**************************@posting.google.c om...
I'll preface this question with my assertion that I'm not
intellectually challenged...(if you ask my wife on a good day she'll
probably agree with you too)

However, this problem is making me feel that way.

I have some data in a QUERY. The columns of interest are:

INTERNALID, NUMBER, etc....
A, 100
A, 120
A, 140
B, 220
B, 210
C, 133
D, 853
D, 233

I want to sum the NUMBER column for each INTERNALID (A, B, C, etc.)
such that this data can be put on a report. Resultant set would be
something like:
A, 360
B, 430
C, 133
D, 1086
OPTION 1: RunningSum in a query?
- I've read the articles in this newsgroup that say this is both poor
practice and can be difficult. I tried this anyway by inserting a
calculated field into the query and had trouble with the format:

PBV: DSum([NUMBER], "tblName", "[INTERNALID] = " & [INTERNALID])

I know the problem is the format of the 3rd argument but don't know
how to correct it.

OPTION 2: Use RunningSum property on the report.
- I don't want a simple running sum, I want a running sum only for
certain conditions (for when INTERNALID = A, B, C, etc). I'm not
having much success formatting this property successfully on the
report.
Which route should I use (option 1 or option 2)? Any code examples
appreciated for the given case...


Use a totals query.

SELECT INTERNALID, Sum(NUMBER) AS Total Group By INTERNALID

By the way, this has nothing to do with a running sum. A running sum is when every
row includes all of the data for the rows that precede it. What you want is simply
to sum Per-INTERNALID. That is exactly what Totals queries are for.
Nov 12 '05 #2

P: n/a
> Use a totals query.

SELECT INTERNALID, Sum(NUMBER) AS Total Group By INTERNALID

By the way, this has nothing to do with a running sum. A running sum is when every
row includes all of the data for the rows that precede it. What you want is simply
to sum Per-INTERNALID. That is exactly what Totals queries are for.


Rick,

Thanks for your help!

I suppose part of my problem was that I incorrectly analyzed what I
really wanted to do... (After using this newsgroup and encountering
the term RS I guess the word just infected my brain. I'll apologize
here to all future readers of this thread looking for help on the RS
topic...)

Thanks again,
Eric
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.