"(PeteCresswell)" <x@y.Invalidwrote in
news:0h********************************@4ax.com:
Can anybody point me to an example of creating a running sum via SQL.
The situation is the application shows a history of bond trades - both
as parent transaction and as allocated among funds. The user wants
to see the current total holdings after each trade - both at the
parent and fund allocation level.
I wimped out and created a "CurrentNetBalance" field in each of the
two tables and maintained it when processing a trade - having jumped
to the conclusion that recalculating same for every row shown would
kill performance.
The obvious has been pointed out to me: that that's a poor solution,
laden with additional maintenance issues when the user wants to delete
or retroactively modify a trade.
I'm pretty sure something like that can be done via SQL on a small
dataset without too much of a performance hit because the SQL below
runs quite quickly. I'd try to adapt that to my needs, but I don't
have a clue how or why it works... and I've also found that it breaks
sometimes.
-------------------------------------------------
SELECT (SELECT Count(*) FROM tlkpCountry as xxx
WHERE xxx.Country < tlkpCountry.Country)+1 AS RowNum,
tlkpCountry.Country FROM tlkpCountry;
-------------------------------------------------
So, bottom line, can somebody aim me at an example?
Not me ...
We'va had a very long running thread here recently about something
similar; someone wants to update each row, or display for each row, data
calculated on the basis of other rows, usually preceding rows, however
preceding may be defined.
IMO SQL was not designed with this capability in mind. What are the
solutions?
1. Abuse the SQL so that it is recursive and calculates for each row the
required summmative data for all preceding rows as per your example; as
the number of rows increases this gets slower and slower as it requires
recalculation on something like n(n+1)/2 rows;
2. Open a recordset and scan through it, updating a summative field or
fields; this is as current as loading the dataset, which is often not
current enough, especially if the scan is not instantaneous; but a scan
is a scan, it's not a repetitive scan, that is row 1 is or should be
looked at once and once only; some people think that disconnecting a
recordset makes this solution more digestible;
3. Display the data in a report and use the report's running sum
capbilities;
4. If we are using MS-SQL or similar powerful engine we can hide all of 1
above in a Stored Procedure or UDF as well as do it more efficiently; all
the work will be done on the server; many say this is a good thing; I am
not so sure; while your system is idle do you want the server's system to
be doing the work of 100 users like you?
5. I don't know if cross tabs are a solution to this;
6. Combine the notion of opening a recordset with the idea of display;
this is done regularly in html display of data and can be done in Access
by setting the form's (or in Access 2003 ADPs report's) recordset to the
opened recordset(ADO). Of course, we can display html in Access if we are
willing to use the WebBrowser Control.
Are there other solutions? I would be very happy to be reminded of them
or to be instructed in them.
IMO the report is the preferred solution in most cases. Reports have very
powerful grouping and summative capabilities. And the client sees but
cannot mess with the results.
--
Lyle Fairfield
