Summing Sequential Records

[frankiem]

I am trying to sum sequential records in a table. My data looks like:

[change] [count]
0 0
1 1
1 2
0 2
0 2
1 3
0 3
1 4

The [count] field is initially blank. I set [count] for record 1 to 0. Then, I wrote some SQL code to compute subsequent [count] values by adding the value of [count] in the previous record to the value of [change] in the current record.

This code works by taking 2 copies of the table and mapping the recordnumber key, [tbl].[ID] to [tbl_1].[ID+1]. However, this code only works intermittently and I don't understand why - I think ACCESS might be slicing the job up into bits and parallel-processing them.

Does anybody know what causes this problem - and, more to the point, how can I fix it?

FrankieM

 

[swampBoogie]

select change,
       (select sum(change) from t
         where id <= ct.id)
  from t ct
 order by id

 

[frankiem]

swampBoogie,

Do you mean to put this code inside a VB loop, resetting ct each time? I have several million records in these tables, so I am hoping to find a quicker method than that. My SQL code is pretty quick - when it works! It just doesn't work all the time, i.e. it will work maybe 1 time out of 8. The other 7 times, the first couple of thousand records are computed properly and the rest remains blank.

FrankieM

 

[lespaul]

what swampboogie gave you IS sql! Just change 't' to your table name!

select change,
(select sum(change) from t
where id <= ct.id)
from t ct
order by id


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[frankiem]

Sorry, didn't recognise 'ct' as 'copy of table' (Duh!).

This codse only works for small numbers of records (less than a few thousand). For tables like I'm working on (often with over a million records), it's very slow and causes ACCESS to hang. I have 512MB of RAM on my PC, so I don't think that is the problem. Any more ideas?

 

[DeGeneral]

I see your problem FrankieM. Your table is so big that using the sum command a million times in a subquery overloads the buffer. Your approach of using the table to do the work record-by-record sounds about right, but I can't see why that wouldn't work.

 

[frankiem]

DeGeneral,

That's is exactly it! When I started to work on this query a few weeks ago, I started with subqueries. But they didn't work. Little by little, I changed things until I got the query to work. My query does work - but not all of the time. I can't seem to systematically get it to start at the first record and work through until the end. For example, in a table with 500,000 records, several attempts will only produce values for the first few thousand records. The remaining records will be blank. Then, without any change in inputs or commands, another run will produce exactly what I am looking for - the cumulative sum in each record.

I am beginning to think this one is not solvable. Thanks anyway to everybody who took the time to respond!

FrankieM