| re: RunningSum options: Query or Report?
"Eric Schneider" <eschneider71@hotmail.com> wrote in message
news:662db8c3.0309250930.25c5f7e1@posting.google.c om...[color=blue]
> 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...[/color]
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.
| 
