Are there any ways to speed up a Cumulative sum in a query or perhaps
another faster way to have a cumulative sum column (call a vba
function?).
For example, I want to sum up all values under 'Amount', and also have
a field call 'Cumulative' and have the sum grow with each record. Using
"MyTable", the query results are...
ID or Date Amount Cumulative
--------------- ------ ----------
a 2-3-2099 5 5
b 29-5-2099 3 8
c 12-8-2099 6 14
d 23-9-2099 2 16
My cumulative field uses,
Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates
Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's
This works well for relatively small recordsets, but for aggregate
'Group By' queries or large tables it is painfully slow.
Any ideas or comments?
TIA 4 24384 ce*******@yahoo.com wrote: Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?).
For example, I want to sum up all values under 'Amount', and also have a field call 'Cumulative' and have the sum grow with each record. Using "MyTable", the query results are...
ID or Date Amount Cumulative --------------- ------ ---------- a 2-3-2099 5 5 b 29-5-2099 3 8 c 12-8-2099 6 14 d 23-9-2099 2 16
My cumulative field uses,
Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates
Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's
This works well for relatively small recordsets, but for aggregate 'Group By' queries or large tables it is painfully slow.
Any ideas or comments?
TIA
The domain aggregate functions can be painfully slow.
You could try using a subquery to define your "cumulative" field instead
of using a function. Your query SQL might look something like this:
SELECT
mytable.mydate,
Sum(mytable.amount) AS SumOfamount,
(select sum(amount) from mytable as mytablecopy
where mydate<=mytable.mydate) AS cumulative
FROM mytable
GROUP BY mytable.mydate;
I think this should run reasonably quickly as long as your date field is
indexed.
Or, there are much faster replacements for Access' slow built in domain
aggregate functions which are available from http://www.mvps.org/access/modules/mdl0012.htm ce*******@yahoo.com wrote: Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?).
For example, I want to sum up all values under 'Amount', and also have a field call 'Cumulative' and have the sum grow with each record. Using "MyTable", the query results are...
ID or Date Amount Cumulative --------------- ------ ---------- a 2-3-2099 5 5 b 29-5-2099 3 8 c 12-8-2099 6 14 d 23-9-2099 2 16
My cumulative field uses,
Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates
Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's
This works well for relatively small recordsets, but for aggregate 'Group By' queries or large tables it is painfully slow.
Any ideas or comments?
Dsum() is definately very slow especially for large data sets.
I'm sure there is a nice dynamic way to do it... but...
You can always dump the data into a temporary table and run some code
through it to do the summing. It can be remarkable quick this way.
Br@dley
As others have pointed out, domain funnctions are slow, but you have a more
fundamental problem. The time it takes to perform your sum is proportional to
n squared where n is the number of records. If you do it for 1000 records,
you've had to sum (I believe) 500500 records. That's why SQL doesn't do
running sums very well.
Workarounds:
1. Only do running sums on reports. Access will do a running sum for you
that's generated be keeping a running sum in memory as the rows are read from
the query.
2. Use a temporary result. Here, you first create a result table with an
autoincrement field, use an insert query to populate the table in order
(leaving the running sum blank), then use code to open a recordset on the
result table, loop through the rows using the autoincrement field as the sort
order, keep a running total, and write to the running total to the running sum
field.
On 20 Apr 2005 00:34:19 -0700, "ce*******@yahoo.com" <ce*******@yahoo.com>
wrote: Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?).
For example, I want to sum up all values under 'Amount', and also have a field call 'Cumulative' and have the sum grow with each record. Using "MyTable", the query results are...
ID or Date Amount Cumulative --------------- ------ ---------- a 2-3-2099 5 5 b 29-5-2099 3 8 c 12-8-2099 6 14 d 23-9-2099 2 16
My cumulative field uses,
Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates
Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's
This works well for relatively small recordsets, but for aggregate 'Group By' queries or large tables it is painfully slow.
Any ideas or comments?
TIA
CEFrancke,
I dumped the idea of doing cumulative or running sums in queries and went
with building a table where I store my cumulative totals. Since it's stored
as a calculated result all a query has to do is retrieve the row--very fast.
I also index my totals table because I am more interested in faster results
in querying than I am in faster posting of results. Sounding like data
warehouse technique? Yup.
--
Alan Webb kn*******@SPAMhotmail.com
"It's not IT, it's IS"
<ce*******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com... Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?).
For example, I want to sum up all values under 'Amount', and also have a field call 'Cumulative' and have the sum grow with each record. Using "MyTable", the query results are...
ID or Date Amount Cumulative --------------- ------ ---------- a 2-3-2099 5 5 b 29-5-2099 3 8 c 12-8-2099 6 14 d 23-9-2099 2 16
My cumulative field uses,
Cumulative: DSum("[Amount]","MyTable","Date<=" & Date) 'For Dates
Cumulative: DSum("[Amount]","MyTable","ItemID<=" & ID) 'For ID's
This works well for relatively small recordsets, but for aggregate 'Group By' queries or large tables it is painfully slow.
Any ideas or comments?
TIA This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tim Fountain |
last post by:
We've recently enabled slow query logging on a server and it's proving
interesting seeing which queries are bogging things down. This one is
puzzling me a little:
SELECT articleid, type,...
|
by: Yannick Turgeon |
last post by:
Hello all,
I've got a query which suddently became very slow. It now takes about 10
secs instead of 2 secs.
I've got to identical DB (one is for test and the other is production). The
query...
|
by: Matt Larkin |
last post by:
I am pulling my hair out trying to solve this one (presumably because
I am not particularly trained or skilled at access!)
I have a query which summarises the variances that each of my sales...
|
by: Janross |
last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing
office computer it's fine (well, 2-4 seconds), but on the client's network, it
takes at least 5 minutes to run. ...
|
by: Victor |
last post by:
Hi There,
I have a query witch gives me the following result:
Period NumberOfItems
1 13
2 2
3 1
4 1
5 1
|
by: MikeSA |
last post by:
Hi
I a trying to create a chart that reflects monthly cumulative totals from a query.
The query fields show sales opportunities forecasted invoice date (OppForInvDate), Opportunity Description and...
|
by: ChaseCox |
last post by:
Hi all,
I have a problem that I have been looking at for a couple days now and I can not quite get it to work.
I would like to calculate the cumulative percent failure of a certain product in...
|
by: wisemen |
last post by:
I have a table with 2 columns, and .I want to run a query that will give me a cumulative sum of the no. of entries that have an <= and on the second column give me a cumulative sum of the no. of...
|
by: existential.philosophy |
last post by:
This is a new problem for me: I have some queries that open very
slowly in design view.
My benchmark query takes about 20 minutes to open in design view. That
same query takes about 20 minutes...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |