running total by groups

[Elaine]

Hi,

I tried the folowing query, but for the runnging total I am getting
"#Error"as the results.

SELECT WC.[Member ID] AS MembAlias, WC.fund AS FundAlias, WC.Service_Month
AS MonthAlias, Sum(WC.SumOfPaidAmt) AS SumAmt,
DSum("SumOfPaidAmt","WC","[Member ID])=" & [MembAlias] & "[fund]=" &
[FundAlias] & "[Service_Month]<=" & [MonthAlias] & "") AS RunTot
FROM MBR_FUND_COSTS AS WC
GROUP BY WC.[Member ID], WC.fund, WC.Service_Month
ORDER BY WC.[Member ID], WC.fund, WC.Service_Month;


I alternatively use this other query to get the results and it runs as a
Select Query but it stays forever runnging as a Make Table Query, which I
need to do.

SELECT WC.[Member ID], WC.fund, WC.Service_Month, WC.SumOfPaidAmt AS SumAmt,
(SELECT sum(SumOfPaidAmt)
FROM MBR_FUND_COSTS
WHERE [Member ID]=WC.[Member ID]
and fund=wc.fund
And Service_Month<=WC.Service_Month
) AS RunningTot
FROM MBR_FUND_COSTS AS WC
GROUP BY WC.[Member ID], WC.fund, WC.Service_Month, WC.SumOfPaidAmt
ORDER BY WC.[Member ID], WC.fund, WC.Service_Month;


Please help.

Thanks

 

[S.Clark]

My guess would be that the DSum is returning a Null, which translates to
#Error in the query. Wrap it in the Nz() function to handle those cases.

Nz(DSum(,,),0)

 

[Elaine]

Hi, It still gives the same error