On Oct 3, 4:36*pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Sun, 3 Oct 2010 13:16:40 -0700 (PDT), Elizabeth Gallagher
>
>
>
>
>
> <emgallaghe...@gmail.com> wrote:
> >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/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../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!
Similar Threads
