Help with DSum to create running sum

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I am trying to create a query that will show a running
sum of a calculated field as this query builds a chart.
Here is my SQL prior to adding a DSum expression:

SELECT Left([Activity],12) AS ActivityName,
[CostSavingsperYear]/250000 AS CostReduction,
10.37 AS Target
FROM Activity
GROUP BY Left([Activity],12),
[CostSavingsperYear]/250000, 10.37
ORDER BY [CostSavingsperYear]/250000;

(The Activity field in the Table is a memo field so I
am only collecting the first 12 characters as the
value on the X-axis for the chart.)

I want to create a running sum of the
Cost Reduction field, but am stuck on the
DSum expression. I tried:

RunTotal:DSum("CostReduction","qryCostReduction",
"[Activity]<=" & [ActivityName] & "")

No doubt I cannot use the DSum against fields in the query.
So how do I achieve the desired results? Dies anyone
have any advice?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes you can use query fields in the DSum() function:

   RunTotal:DSum("CostSavingsperYear/250000","Activity",
     "Left(Activity,12) = '" & Left(A.Activity,12) & "'")

You'll have to alias the table Activity as A in the main query for this
to work.  It would look like this in the SQL view:

   FROM Activity As A

In Design View you'd right click on the table and select Properties -
then change the Alias property from the table name to it's Alias - A.

Notice I used single quotes to delimit the Left(A.Activity,12) in the
DSum() function, 'cuz it returns a string that will be compared to
Left(Activity,12).

BTW, you shouldn't have a column (field) with the same name as the
table, it can be confusing; and, it doesn't truly identify the column's
data:  what type of Activity?  ion_activity, event_activity,
game_activity, etc.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSRom1IechKqOuFEgEQIm8wCdF03y32gfnBCuQKWqyOvnfxzXUssAoOy+
UloY963C6IwiBN3CW68ar8El
=choc
-----END PGP SIGNATURE-----


I am trying to create a query that will show a running
sum of a calculated field as this query builds a chart.
Here is my SQL prior to adding a DSum expression:
Click to expand...
SELECT Left([Activity],12) AS ActivityName,
[CostSavingsperYear]/250000 AS CostReduction,
10.37 AS Target
FROM Activity
GROUP BY Left([Activity],12),
[CostSavingsperYear]/250000, 10.37
ORDER BY [CostSavingsperYear]/250000;
Click to expand...
(The Activity field in the Table is a memo field so I
am only collecting the first 12 characters as the
value on the X-axis for the chart.)
Click to expand...
I want to create a running sum of the
Cost Reduction field, but am stuck on the
DSum expression.  I tried:
Click to expand...
RunTotal:DSum("CostReduction","qryCostReduction",
"[Activity]<=" & [ActivityName] & "")
Click to expand...
No doubt I cannot use the DSum against fields in the query.
So how do I achieve the desired results?  Dies anyone
have any advice?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for your help, MG.... but I still have a problem.

You stated that I need to:

"In Design View you'd right click on the table and select Properties
-
then change the Alias property from the table name to it's Alias - A."

I cannot find that option. I am running Access 2003, btw.

Secondly, yes you are correct, I should rename the Activity field, and
I have - to CostActivity so my SQL for the query looks like:

SELECT Left([CostActivity],12) AS ActivityName,
[CostSavingsperYear]/250000 AS CostReduction,
10.37 AS Target,
DSum("CostSavingsperYear/250000","Activity","Left(CostActivity,12) =
'" & Left([A].[CostActivity],12) & "'") AS RunTotal
FROM Activity AS A
GROUP BY Left([CostActivity],12), [CostSavingsperYear]/250000, 10.37
ORDER BY [CostSavingsperYear]/250000;

Unfortunately, I am getting the same values in both the
CostSavingsperYear
field and the RunTotal field. The RunTotal field is not producing a
running
sum. Can you see the problem?
 
So I adjusted the statement to read:

RunTotal: DSum("CostSavingsperYear/250000","Activity",
"Left(CostActivity,12) <= '" & Left(A.CostActivity,12) & "'")

and by added the "<" to the query I get a running sum, but
it is still incorrect. Below are my query results:

ActivityName CostReduction Target RunTotal
ActivityA 0.96 10.37 0.96
ActivityB 0.96 10.37 3.84
ActivityC 0.96 10.37 4.8
ActivityD 1.92 10.37 2.88
ActivityE 1.92 10.37 6.72

I thought perhaps the RunTotal field was ordering
by the order of entry in the table, but its not. There
doesn't seem to be a rhyme or reason (that I can see)
for this order. It should be:

0.96
1.92
2.88
4.80
6.72

Any thoughts?
 
I have not follow the whole thread, but it seems you need:

RunTotal: DSum("CostSavingsperYear/250000","Activity",
"ActivityName <= '" & ActivityName & "'")


Vanderghast, Access MVP
 
In the data sample you supplied, that is your first column.


Vanderghast, Access MVP
 
In the data sample you supplied, that is your first column.

Vanderghast, Access MVP







- Show quoted text -
Click to expand...

I know, but it based on:

SELECT Left([CostActivity],12) AS ActivityName

CostActivity is actually the field name in the table.
 
Your query will take forever to run if your names are 'coded'. Furthermore,
are you sure these computed ActivityName are UNIQUE (else, if there are dup,
your 'running' is not uniquely defined). Why not make a first query with:

SELECT Left([CostActivity],12) AS ActivityName,
SUM(whaterverExpression) AS CostReduction
FROM ...
GROUP BY Left([CostActivity],12)


save it, say,under the name of q1, then use it, in another query, with the
DSum:

SELECT ActivityName,
DSUM("CostReducction", "q1", "ActivityName<=""" & ActivityName &
"""" ) As runningSum
FROM q1
ORDER BY ActivityName


Vanderghast, Access MVP



In the data sample you supplied, that is your first column.

Vanderghast, Access MVP







- Show quoted text -
Click to expand...

I know, but it based on:

SELECT Left([CostActivity],12) AS ActivityName

CostActivity is actually the field name in the table.
 
Sorry, no...that does not produce the results I am looking for.

Here is my SQL:

q1:

SELECT Left([CostActivity],12) AS ActivityName,
Sum([CostSavingsperYear]/250000) AS CostReduction
FROM Activity
GROUP BY Left([CostActivity],12)
ORDER BY Sum([CostSavingsperYear]/250000);

and here is the second one:

SELECT q1.ActivityName,
DSum("CostReduction","q1",
"ActivityName<=""" & [ActivityName] & """") AS RunTotal
FROM q1
ORDER BY q1.ActivityName;

And here are my results:

ActivityC --- 0.96
ActivityE --- 2.88
ActivityB --- 3.84
ActivityA --- 4.8
ActivityD --- 6.72

The ORDER BY should actually be:

ORDER BY DSum("CostReduction","q1","ActivityName<=""" & [ActivityName]
& """");

But then the order is wrong, as it should be in the same order as q1
and the
DSum figures are incorrect which is my biggest concern.

the running sum results should be:

0.96
1.92
2.88
4.80
6.72

Why does 3.84 keep coming up and what happened to 1.92
 
Strange, why do you Left( , 12) if the ActivityName has only 9
characters? What are the 3 other characters? Are they spaces, or
unprintable character...? Anyhow, if you have a working solution...

Vanderghast, Access MVP
 
Just an FYI, the "ActivityA, B...etc" I used in my email were just
examples, not the real
activity names. I could not provide the real activity names as the
database information
is proprietory. Thank you for your support.
 
Back
Top