Is it possible to have a running sum

[bknight]

I have a Db where it is designed like
1 field 2 field 3 field with field x as a number.
Is it possible to design a query to sum the numbers in sequence.
1 field 2, field 3, number x, sum x
2 field 2, field 3, number y, sum x+y
3 field 2, field 3, number z, sum x+y+z
etc.

[HansV]

Does the table have an AutoNumber field, or another unique field by which we can sort the records?

[bknight]

Yes the first field 1, 2 ,3 etc.

[HansV]

It could look like this:

SELECT Field1, Field2, ..., NumberField, DSum("NumberField", "TableName", "Field1<=" & [Field1]) AS RunningSum
FROM TableName
ORDER BY Field1

[bknight]

After correcting my fat fingers, worked to an extent. Two issues first I wanted to take a slice of the data with a criteria, however the Dsum looks like it used record 1 as the starting sum. Second after creating a form and formatting the number field and the RunningSum field as Currency only the Number field was formatted correctly. And I limited the RunningSum to 2 decimals, and that failed also.

[HansV]

You know what I'm going to ask you next...

[bknight]

Nope.

[HansV]

Please attach or send a copy of the database...

[HansV]

I have replied to your email.

[bknight]

Your comment concerning attempting to do two actions at once is understandable.

The query was trying to do two things at once: filter the table AND creating a running sum.
I removed the running sum field from quTradeRunningSumProfit, and created a new query quTradeRunningSumProfit2 based on it and added the running sum field there, adjusted of course.
quTradeRunningSumProfit2 is now the record source of the form.

However would that negate the format of the query?

[HansV]

Does the query in the database that I sent back do what you want?

[bknight]

Haven't tried it yet been busy, I'll post again when I open it.

[HansV]

[bknight]

Can't open the Db, unrecognized format.

[HansV]

I have sent it to you in a zip file. Does that work?

[HansV]

The SQL for quTradeRunningSumProfit:

SELECT NinjaTrader2024.NumRec, NinjaTrader2024.Instrument, NinjaTrader2024.Action, NinjaTrader2024.Quantity, NinjaTrader2024.Price, NinjaTrader2024.Time, NinjaTrader2024.Ent_Ex, NinjaTrader2024.Profit
FROM NinjaTrader2024
WHERE (((NinjaTrader2024.NumRec) Between 275 And 300) AND ((NinjaTrader2024.Profit) Is Not Null))
ORDER BY NinjaTrader2024.NumRec;

The SQL for quTradeRunningSumProfit2 (now the Record Source of frmTradeRunningSumProfit):

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

Set the Format property of the RunningSum column to Currency.

[bknight]

Everything else is the same except for those two queries/forms?
And I suppose there is a difference between my 2007 and your 365?

[HansV]

I didn't change anything else.

Microsoft has added new features to Access since 2007, but the database format has remained the same, so I don't understand why you cannot open the database...

[bknight]

Maye the extra features aren't recognized and that causes the error.

[bknight]

Yep that is what I was looking for.
Thanks