Is it possible to have a running sum

[bknight]

Its been a while since I used this. I change the fire query to

Code: Select all

SELECT Int(([NinjaTrader2024].[Time])) AS TradeDate, Sum(NinjaTrader2024.Profit) AS TotDaily, Sum(Abs([quantity])) AS Trs, Sum([commission])+Sum([fees]) AS [Com&Fee], NinjaTrader2024.NumRec
FROM NinjaTrader2024
WHERE (((Int(([NinjaTrader2024].[Time])))>=Date()-Weekday(Date())+1 And (Int(([NinjaTrader2024].[Time])))<Date()-Weekday(Date())+8))
GROUP BY Int(([NinjaTrader2024].[Time])), NinjaTrader2024.NumRec, NinjaTrader2024.NumRec
HAVING (((Sum(NinjaTrader2024.Profit)) Is Not Null));

The second query is the same

Code: Select all

SELECT quTradeRunningSumProfit.*, Val(DSum("Profit","quTradeRunningSumProfit","NumRec<=" & [NumRec])) AS RunningSum
FROM quTradeRunningSumProfit;

The query runs with running sum resulting in #error. Can anyone see a correction that would allow the Dsum to run accurately?

[HansV]

Since you removed NumRec from the fields to be returned by the first query, the second query does not recognize NumRec

[bknight]

NumRec is present actually twice, so I removed one of them.

Code: Select all

SELECT Int(([NinjaTrader2024].[Time])) AS TradeDate, Sum(NinjaTrader2024.Profit) AS TotDaily, Sum(Abs([quantity])) AS Trs, Sum([commission])+Sum([fees]) AS [Com&Fee], NinjaTrader2024.NumRec
FROM NinjaTrader2024
WHERE (((Int(([NinjaTrader2024].[Time])))>=Date()-Weekday(Date())+1 And (Int(([NinjaTrader2024].[Time])))<Date()-Weekday(Date())+8))
GROUP BY Int(([NinjaTrader2024].[Time])), NinjaTrader2024.NumRec
HAVING (((Sum(NinjaTrader2024.Profit)) Is Not Null));

[HansV]

But is is not in the SELECT clause - it was present there in a previous version.

[bknight]

How is this not a select
partial code
SELECT Int(([NinjaTrader2024].[Time])) AS TradeDate, Sum(NinjaTrader2024.Profit) AS TotDaily, Sum(Abs([quantity])) AS Trs, Sum([commission])+Sum([fees]) AS [Com&Fee], NinjaTrader2024.NumRec

[HansV]

Sorry, I didn't see NumRec in your SQL because it required scrolling to the right, so I thought you had removed it.
As usual: please send me the database.

[HansV]

Ah. I saw that but was distracted. The sum of the Profit field in the first query is named TotDaily, so you should use that in the DSum expression:

SELECT quTradeRunningSumProfit.*, Val(DSum("TotDaily","quTradeRunningSumProfit","NumRec<=" & [NumRec])) AS RunningSum
FROM quTradeRunningSumProfit;

[bknight]

I entered TotDaily as the sum of Profits, that messed me up. Thanks