Running Total or sum error in query

[Ken]

Hi,

When using DSUM in query to get running total, it works.
Problem is without dsum getting error.
Error is 'Tried to execute a query that does not include the specified expression 'Total_Amt' as part of Aggregate function'

SQL having error:

select a.YrMth, sum(a.Total_Amt) as aTotalAmt,
       (select sum(b.Total_Amt) as bTotalAmt
        from   TableInv b
        where  b.Total_Amt <= a.Total_Amt) as rTotalAmt

from  TablInv a
group by a.YrMth;

[Question: what is missing or is it always, in ACCESS require DSUM to get running total].
Looking output as YearMonth, Total, RunningTotal

Appreciate your help.

Thanks,

TechIT

 

[dhookom]

I don't understand your requirement. I would think the subquery would filter based on YrMth rather that Total_Amt.

SELECT TableInv.YrMth, TableInv.Total_Amt, Sum(TableInv_1.Total_Amt) AS SumOfTotal_Amt
FROM TableInv, TableInv AS TableInv_1
WHERE (((TableInv_1.YrMth)<=[TableInv].[YrMth]))
GROUP BY TableInv.YrMth, TableInv.Total_Amt;

Duane
Hook'D on Access
MS Access MVP

 

[Ken]

Hi dhookom,

Thanks your query helped and works.

TechIT