Re: Running total problems (Currently using DSum function)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

"Duane Hookom" wrote:

> 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
>
>
> "EddieC" <EddieC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:2A5BA1CB-6A8D-4E88-864C-5BFDB8337142@xxxxxxxxxxxxxxxx
> > 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];
> >
>
>
>
.

Relevant Pages

  • Re: Cross Tab Query with two value=-[Result] & " - " & [Error] as
    ... Duane & Vincents, ... Anyway, the Union Query now has 6 SELECT parts, not 3, and each one ... SELECT ID,Client,Date, ResultA as Value1, "ResultA" as Task ...
    (microsoft.public.access.queries)
  • Re: Calculate a percentage using 2 different query totals
    ... I you get prompted for Customer ID then you don't have a field in your ... Duane Hookom ... > query, however, when I click to run the query a popup box appears and asks ... Product Purchase Information ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Report to Include All Possible Values from CrossTab Query
    ... Again, thanks Duane. ... exam, medical exam, etc for police recruits). ... summarizes the outcomes by gender. ... in the data query and tblOutcomes is connected to a field named Exam_Med ...
    (microsoft.public.access.reports)
  • Re: Pivot Table in Report - Duane H.
    ... statement in the SQL, I still get the Microsoft Jet error message. ... Duane Hookom ... "Claire" wrote in message ... > I have used a form to set a year parameter, the query needs to see the ...
    (microsoft.public.access.queries)
  • Re: Beginner query help please
    ... I've been poking around Outlook and your programmes query output, ... I've noticed that if you select all the query output (in this case ... Duane Hookom wrote: ...
    (microsoft.public.access.queries)