Re: Help with DSum to create running sum

On Nov 11, 7:44 pm, MGFoster <m...@xxxxxxxxxxx> wrote:
-----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-----



Opal wrote:
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?- Hide quoted text -

- Show quoted text -

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?
.

Relevant Pages

  • Re: Help with DSum to create running sum
    ... You'll have to alias the table Activity as A in the main query for this ... DSum() function, 'cuz it returns a string that will be compared to ... Here is my SQL prior to adding a DSum expression: ...
    (microsoft.public.access.queries)
  • Re: Tips on domain aggregate replacements
    ... This already is a split db, but the users are 400 miles away from the server hosting the data:) They are all running the same copy of the FE locally on a single terminal server via TS/RDP sessions. ... It doesn't seem to have much impact on performance; the report takes just about as long to run if they are all logged in as it does when I am logged on testing it at night. ... I would do DSums from the controls on the report on the data returned by the query. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Field aliases not being returned properly in A2007
    ... I was wondering if you found a fix for the alias ... Sharepoint Lists as my data repository for about 3 months now. ... This has totally delayed the project I'm working on, I need this query to ...
    (microsoft.public.access.queries)
  • Re: Consumption during time window
    ... Your other option is to use the DSUM function and figure out how to work ... forcing the format of the dates in the DSUM to an unambiguous format. ... must use an updateable query". ... FROM [T1 Daily Consumption] as Temp ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... If the query was "within" SQL Server, then alias ... gets treated differently than in a Jet query. ...
    (microsoft.public.access.queries)
Loading