Is it possible to have a running sum
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Is it possible to have a running sum
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.
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.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
Does the table have an AutoNumber field, or another unique field by which we can sort the records?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to have a running sum
Yes the first field 1, 2 ,3 etc.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
It could look like this:
SELECT Field1, Field2, ..., NumberField, DSum("NumberField", "TableName", "Field1<=" & [Field1]) AS RunningSum
FROM TableName
ORDER BY Field1
SELECT Field1, Field2, ..., NumberField, DSum("NumberField", "TableName", "Field1<=" & [Field1]) AS RunningSum
FROM TableName
ORDER BY Field1
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to have a running sum
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
You know what I'm going to ask you next...
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
Please attach or send a copy of the database...
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to have a running sum
Your comment concerning attempting to do two actions at once is understandable.
However would that negate the format of the query?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.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
Does the query in the database that I sent back do what you want?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to have a running sum
Haven't tried it yet been busy, I'll post again when I open it.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to have a running sum
Can't open the Db, unrecognized format.
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
I have sent it to you in a zip file. Does that work?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to have a running sum
Everything else is the same except for those two queries/forms?
And I suppose there is a difference between my 2007 and your 365?
And I suppose there is a difference between my 2007 and your 365?
-
- Administrator
- Posts: 78678
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to have a running sum
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...
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...
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to have a running sum
Maye the extra features aren't recognized and that causes the error.
-
- BronzeLounger
- Posts: 1428
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to have a running sum
Yep that is what I was looking for.
Thanks
Thanks