|
| Iniciado por guest, 04,feb. 2009 23:34 - 3 respuestas |
| |
| | | |
|
| |
| Publicado el 04,febrero 2009 - 23:34 |
Has anyone tried including a running total column in a table? Any ideas would be helpful.
Thanks
issah |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,febrero 2009 - 16:07 |
Hi Issah...
to do this, you will have to either use a memory table or strictly limit what functionality are available in your file/query table....
In a memory table, you just have to calculate the running total and add it as you add your lines...
In a file table, you would have to calculate/add the running total when you DISPLAY the line, and the problem is of course that records are read only when a line needs to be displayed, so if you allow the SEARCH function in your table, the user will arrive on a record without reading the previous ones, and you won't be able to calculate your total...
Same problem with the sort capabilities (but that is true also in a memory table,where yowould have to recalculate the total for each sort)
Best regards |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,febrero 2009 - 18:57 |
Thanks and will give it a try.
Regards
issah |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
71 mensajes |
|
| Publicado el 14,diciembre 2015 - 10:26 |
Hi,
I was just working on running total and I have two solutions, each has some pros and cons. I am using memory table.
1. SQL SELECT
Select below will return table with column Balance cointaining running total.
Be aware that OVER comand probably will not work on any SQL server. This one works on MS SQL. You can quite easily find alternative SELECT that does the same, just google something like "running total in SQL".
Btw. I have 2 columns for money transfers. Income and Expenses, you can work with one as well, you just have to change SELECT.
SELECT Income, Expenses, SUM(Income) OVER(ORDER BY ItemCode) -SUM(Expenses) OVER(ORDER BY ItemCode) AS Balance
FROM Cash
ORDER BY ItemCode ASC
2. WINDEV CODE
I have this code. You should run it AFTER table is filled with data. I had it before in "Displaying a row" event and it does not work very well, I do not want to go to details, but it works on the 1st look but when you start to use it you will find out that it gives "strange" results.
Procedure CountBalance()
nId is int
WITH WIN_1Cashbook.TABLE_Cashbook
FOR EACH ROW OF WIN_1Cashbook.TABLE_Cashbook
nId = WIN_1Cashbook.TABLE_Cashbook
IF nId > 1 THEN
.COL_Balance=.COL_Balance[nId-1]+.COL_Income-.COL_Expenses
ELSE
.COL_Balance=.COL_Income-.COL_Expenses
END
END
END
IF nId > 1 THEN; if 1st row is being processed, there is no previous Balance, so this row takes care of it
Of course you should solve situations when user will sort a table or similar situations.
Petr |
| |
| |
| | | |
|
| | | | |
| | |
|