Running total problems (Currently using DSum function)

[EddieC]

Hi,
I am trying to use Dsum (Access 97) in a query, to create a running total of
the field 'percentageOfTotalDecayedActivity' from the query
'PM_Activity_Percentages'
I have followed microsoft knowledge base article 138911 but the query
produces a zero value in the running total field 'runtot'.

I believe this may be to do with the fact that my data set contains multiple
'percentageOfTotalDecayedActivity' values for any one date, but I don't know
how best to solve it!
Please could someone help me with this, or advise me of a better way to do it.
Thanks,
EddieC.

Here is the SQL statement produced by the query at present:

SELECT
DSum("PercentageOfTotalDecayedActivity","PM_Activity_Percentages","[Collection
Confirmed Date]<=" & [DateAlias] & "") AS Runtot,
PM_Activity_Percentages.[Collection Confirmed Date] AS DateAlias
FROM PM_Activity_Percentages
GROUP BY PM_Activity_Percentages.[Collection Confirmed Date]
ORDER BY PM_Activity_Percentages.[Collection Confirmed Date];

 

[Duane Hookom]

While I don't care for using DSum() in a query like this, try:
SELECT
DSum("PercentageOfTotalDecayedActivity","PM_Activity_Percentages",
"[Collection Confirmed Date]<=#" & [Collection Confirmed Date] & "#") AS
Runtot,
PM_Activity_Percentages.[Collection Confirmed Date] AS DateAlias
FROM PM_Activity_Percentages
GROUP BY PM_Activity_Percentages.[Collection Confirmed Date]
ORDER BY PM_Activity_Percentages.[Collection Confirmed Date];

 

[EddieC]

Thanks for trying to help Duane, but it still didn't work. The runtot field
just prints zero's. Could it be to do with the fact that the information
being summed comes froma calculated field in another query?
Failing that, could you suggest a method that will produce a running total
without
using Dsum?

Thanks,
Ed.

While I don't care for using DSum() in a query like this, try:
SELECT
DSum("PercentageOfTotalDecayedActivity","PM_Activity_Percentages",
"[Collection Confirmed Date]<=#" & [Collection Confirmed Date] & "#") AS
Runtot,
PM_Activity_Percentages.[Collection Confirmed Date] AS DateAlias
FROM PM_Activity_Percentages
GROUP BY PM_Activity_Percentages.[Collection Confirmed Date]
ORDER BY PM_Activity_Percentages.[Collection Confirmed Date];

--
Duane Hookom
MS Access MVP

Hi,
I am trying to use Dsum (Access 97) in a query, to create a running total
of
the field 'percentageOfTotalDecayedActivity' from the query
'PM_Activity_Percentages'
I have followed microsoft knowledge base article 138911 but the query
produces a zero value in the running total field 'runtot'.

I believe this may be to do with the fact that my data set contains
multiple
'percentageOfTotalDecayedActivity' values for any one date, but I don't
know
how best to solve it!
Please could someone help me with this, or advise me of a better way to do
it.
Thanks,
EddieC.

Here is the SQL statement produced by the query at present:

SELECT
DSum("PercentageOfTotalDecayedActivity","PM_Activity_Percentages","[Collection
Confirmed Date]<=" & [DateAlias] & "") AS Runtot,
PM_Activity_Percentages.[Collection Confirmed Date] AS DateAlias
FROM PM_Activity_Percentages
GROUP BY PM_Activity_Percentages.[Collection Confirmed Date]
ORDER BY PM_Activity_Percentages.[Collection Confirmed Date];

 

[Duane Hookom]

When you view PM_Activity_Percentages in datasheet view, is the
PercentageOfTotalDecayedActivity left or right aligned? Is the Collection
Confirmed Date left or right aligned?

--
Duane Hookom
MS Access MVP

Thanks for trying to help Duane, but it still didn't work. The runtot
field
just prints zero's. Could it be to do with the fact that the information
being summed comes froma calculated field in another query?
Failing that, could you suggest a method that will produce a running total
without
using Dsum?

Thanks,
Ed.

While I don't care for using DSum() in a query like this, try:
SELECT
DSum("PercentageOfTotalDecayedActivity","PM_Activity_Percentages",
"[Collection Confirmed Date]<=#" & [Collection Confirmed Date] & "#") AS
Runtot,
PM_Activity_Percentages.[Collection Confirmed Date] AS DateAlias
FROM PM_Activity_Percentages
GROUP BY PM_Activity_Percentages.[Collection Confirmed Date]
ORDER BY PM_Activity_Percentages.[Collection Confirmed Date];

--
Duane Hookom
MS Access MVP

Hi,
I am trying to use Dsum (Access 97) in a query, to create a running
total
of
the field 'percentageOfTotalDecayedActivity' from the query
'PM_Activity_Percentages'
I have followed microsoft knowledge base article 138911 but the query
produces a zero value in the running total field 'runtot'.

I believe this may be to do with the fact that my data set contains
multiple
'percentageOfTotalDecayedActivity' values for any one date, but I don't
know
how best to solve it!
Please could someone help me with this, or advise me of a better way to
do
it.
Thanks,
EddieC.

Here is the SQL statement produced by the query at present:

SELECT
DSum("PercentageOfTotalDecayedActivity","PM_Activity_Percentages","[Collection
Confirmed Date]<=" & [DateAlias] & "") AS Runtot,
PM_Activity_Percentages.[Collection Confirmed Date] AS DateAlias
FROM PM_Activity_Percentages
GROUP BY PM_Activity_Percentages.[Collection Confirmed Date]
ORDER BY PM_Activity_Percentages.[Collection Confirmed Date];

 

[Jeana]

I am having the same problem with my Dsum function in Access. Did you
ever find a solution? Did you discover a different method to obtain
your running total? My results return the grand total in all rows
instead of a running sum. I have racked my brain... I would GREATLY
appreciate any advice you have.

Thanks in advance for your help.

 

[strive4peace]

Hi Jeana,

What kind of data are you using dSum on to provide a Running
Total? In other words, what is the relevant data structure
(tables and fields) and what is the criteria for limiting
the results of the sum?

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day