cumulative total

[afshin]

I want to prepare some chart that show cumulative total of one field.

When I prepare the report that show accumulated of one field, I use the running sum property of field.

(A running sum is a total that is accumulated from record to record across a group, or even across the entire report.)

that is running very fast.

But in query I obliged to use this expression:

DDSB: DSum("[DDispB]";"IncDeviation";"[IPR]>=" & [IPR] & ' And' & "[IDINC]=" & [IDINC] & ' And' & "[readdate]=" & '#' & [readdate] & '#')

And when I use that query for record source of that chart the running speed of that chart decreases. Has any other way that I don’t have running problem?

[rpeare]

I don't believe there is a way to do a running sum in a query other than the method you've hit upon. Dsum is slow because every time you perform the function on one line you basically have to scan the entire dataset to figure out what should be on the current line. Unless there's something new in Access 2007+ to handle this I think you try to restructure your query to handle this problem.

[alansidman]

Take a look at this thread. A solution for running sums is about half way down the thread. But be sure to read the whole thread so that you have a clear understanding.

http://www.mrexcel.com/forum/showthr...t=Running+Sums

Alan

[afshin]

mr.alansidman
when i clicked that link,irecived error message.

[alansidman]

Site is having issues. I could not gain access today, either. Try again later. Probably maintenance.

Alan

[AndreT]

1) Assume you have a query of 2 fields (seq, value)
2) do a cartesian join (no join criteria) of the query to itself with result (seq1, seq2, value2), and condition WHERE seq2<= seq1
the result should be:
(1, 1, value21)
(2, 1, value21)
(2, 2, value 22)
(3, 1, value 21)
(3, 2, value 22)
(3, 3, value 23), etc...
note the 3rd field is the value associated with seq2.
3) group the 2nd query by seq1 and sum (value2), resulting
(1, value 21)
(2, value 21+value 22)
(3, value 21+ value 22+value 23), etc.

The only problem is when you are missing a seq, in that case you'll have to create another lookup table for all available sequences, outer join to query#1 and assign isnull(value) to 0.

Works all the time for me, pretty fast even with large quantity of rows.

[afshin]

i use that way,but running time for that query is more.

[AndreT]

Hmmm..., how many records you have in each table? how long does it take? Is this a client/server setup (data on the network shared drive, program on the user desktop)? If you create a query listing all values of only one of the bigger table, is it fast or slow?

[afshin]

the result of 2nd-query and tables are 450.
running time is nearly 1 minute.and don't running in networke.
but i need to 2end query are grouped by three fieldes.

[AndreT]

Could you provide some sample SQL statements to look at? I can generate some test data and run on my machine to compare.

[afshin]

First query's SQL statement isquery name is '22')

SELECT IncDeviation.INC, IncDeviation.IPR AS IPR1, IncDeviation_1.IPR AS IPR2, IncDeviation_1.IDispA, IncDeviation_1.Fdate
FROM IncDeviation INNER JOIN IncDeviation AS IncDeviation_1 ON (IncDeviation_1.readdate=IncDeviation.readdate) AND (IncDeviation.INC=IncDeviation_1.INC) AND (IncDeviation.IPR>=IncDeviation_1.IPR);


and 2-nd query SQL statement is:


SELECT [22].INC, [22].Fdate, [22].IPR1, Sum([22].IDispA) AS SumOfIDispA
FROM 22
GROUP BY [22].INC, [22].Fdate, [22].IPR1;

[AndreT]

First I created a table with some 480 random data,

Next I created the same qry [22] as yours, except I used "WHERE (IncDeviation.IPR>=IncDeviation_1.IPR)" instead of "AND (IncDeviation.IPR>=IncDeviation_1.IPR)". Either way both queries produced the same 3140 records. Both variations take less than a second. However Access query designer can't interpret the "AND" version.

The I created the same 2nd query identical to yours which generated 1512 records. This query also takes less than a second.

However, I've noticed in qry [22], you joined [readdate], but output doesn't have this field. When I examine its outcome, it has the structure of my 2nd result, but many duplicates because of the [readdate] join.

The result of your 2nd query doesn't look like my 2nd result. It has duplicates from the [readdate] join, you've also added Fdate in the grouping, which messed up the previous join condition. That's not what I expected.

I suspect these 2 queries are not compatible, suggest you design, run query first, review and make sure the result is what you expect, then move on to the next step and do the same thing.

I have Windows 7 running on Intel i7 with Access 2007, but that shouldn't make that much difference.

[afshin]

Fdate is function of readdate.you can replace reddate in 22query with Fdate.i alter 22query by your suggestion. Running time is decreased. But running time is near 10 second.

[AndreT]

How much time does "select * from IncDeviation" take? then find out how much time if you add fdate in the selection?

What I usually do is to build queries one step at a time towards the final result. If one step takes a lot more time than the previous one, I'll make a decision whether it's correct or not, then move forward.

[afshin]

1nd query is running very fast.the 2nd query take some second.