DSum Works Only for First Group

[Elizabeth Gallagher]

The Query:
SELECT Test.Bname, Test.Week, Sum(Test.SCount) AS SumOfSCount,
DSum("scount","test","(iif([bname]='a',[bname],[bname]))<='" & [bname]
& "' And iif([week]=now,[week],[week])<=#" & [week] & "#") AS RunTot
FROM Test
GROUP BY Test.Bname, Test.Week
ORDER BY Test.Bname, Test.Week;

The Current Result:
Bname Week SumOfSCount RunTot
F 9/26/2010 4 4
F 10/3/2010 3 7
F 10/10/2010 1 8
O 9/26/2010 9 13
O 10/3/2010 3 19
O 10/10/2010 12 32

The Desired Result:
Bname Week SumOfSCount RunTot
F 9/26/2010 4 4
F 10/3/2010 3 7
F 10/10/2010 1 8
O 9/26/2010 9 9
O 10/3/2010 3 12
O 10/10/2010 12 24

Does anyoe have any questions? I am willing to do a subquery if
necessary but I am not sure if that would help.

 

[John W. Vinson]

The Query:
SELECT Test.Bname, Test.Week, Sum(Test.SCount) AS SumOfSCount,
DSum("scount","test","(iif([bname]='a',[bname],[bname]))<='" & [bname]
& "' And iif([week]=now,[week],[week])<=#" & [week] & "#") AS RunTot
FROM Test
GROUP BY Test.Bname, Test.Week
ORDER BY Test.Bname, Test.Week;

The Current Result:
Bname Week SumOfSCount RunTot
F 9/26/2010 4 4
F 10/3/2010 3 7
F 10/10/2010 1 8
O 9/26/2010 9 13
O 10/3/2010 3 19
O 10/10/2010 12 32

The Desired Result:
Bname Week SumOfSCount RunTot
F 9/26/2010 4 4
F 10/3/2010 3 7
F 10/10/2010 1 8
O 9/26/2010 9 9
O 10/3/2010 3 12
O 10/10/2010 12 24

Does anyoe have any questions? I am willing to do a subquery if
necessary but I am not sure if that would help.

Now() does not return today's date; it returns the current date and time
accurate to microseconds, so it's very unlikely indeed that [Week] will be
equal to Now. I suspect you might want to try Date() instead of Now().

In addition, your first IIf statement makes no sense. It says

If [bname] is equal to "A" return the value of [bname]
If [bname] is not equal to "A" return the value of [bname]

in other words, just use [bname].

Could you explain the logic?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Elizabeth Gallagher]

The Query:
SELECT Test.Bname, Test.Week, Sum(Test.SCount) AS SumOfSCount,
DSum("scount","test","(iif([bname]='a',[bname],[bname]))<='" & [bname]
& "' And iif([week]=now,[week],[week])<=#" & [week] & "#") AS RunTot
FROM Test
GROUP BY Test.Bname, Test.Week
ORDER BY Test.Bname, Test.Week;

The Current Result:
Bname       Week    SumOfSCount     RunTot
F   9/26/2010            4                 4
F   10/3/2010            3                 7
F   10/10/2010     1                        8
O   9/26/2010            9                 13
O   10/3/2010            3                 19
O   10/10/2010     12                        32

The Desired Result:
Bname       Week    SumOfSCount     RunTot
F   9/26/2010            4                 4
F   10/3/2010            3                 7
F   10/10/2010     1                        8
O   9/26/2010            9                 9
O   10/3/2010            3                 12
O   10/10/2010     12                        24

Does anyoe have any questions?  I am willing to do a subquery if
necessary but I am not sure if that would help.

Now() does not return today's date; it returns the current date and time
accurate to microseconds, so it's very unlikely indeed that [Week] will be
equal to Now. I suspect you might want to try Date() instead of Now().

In addition, your first IIf statement makes no sense. It says

If [bname] is equal to "A" return the value of [bname]
If [bname] is not equal to "A" return the value of [bname]

in other words, just use [bname].

Could you explain the logic?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

I was using those logic statements because it didn't seem to work if I
just used ([week]<=#" & [week] & "#"). I tried including a field
called [weeknew] and doing ([week]<=#" & [weeknew] & "#") but that
didn't seem to work either.

In any case, I was doing more google searches (even though I searched
for hours before posting), and finally found the answer. I was able
to use a subquery to accomplish what I needed:

SELECT a.bname, a.Week, a.SCount, scount+nz((select sum(scount) from
test b where b.ID<a.ID and b.bname=a.bname),0) AS ['Running Total']
FROM test AS a;

Thanks for considering my question!