Cumulative Query

[alex]

Hello,

Got a table look like this:

Time UNITS
8:00 10
9:00 5
10:00 15
11:00 20


Would a query to will cumulate the sum of the units before the current
one so it will look like this:

Time UNITS
8:00 10
9:00 15 (it`s 10 + 5)
10:00 30 (it`s 10 + 5 + 15)
11:00 50 (it`s 10 + 5 + 15 + 20)

it must be simple but i have no clue how

 

[Marshall Barton]

Got a table look like this:

Time UNITS
8:00 10
9:00 5
10:00 15
11:00 20


Would a query to will cumulate the sum of the units before the current
one so it will look like this:

Time UNITS
8:00 10
9:00 15 (it`s 10 + 5)
10:00 30 (it`s 10 + 5 + 15)
11:00 50 (it`s 10 + 5 + 15 + 20)

it must be simple but i have no clue how

This kind of query can be represented in the query design
grid:

SELECT T.time,
(SELECT Sum(X.units)
FROM table As X
WHERE X.time <= T.time) As RunUnits
FROM table As T

OTOH, this kind should be more efficient, especially if the
time field is indexed, but it can only be done in SQL view:

SELECT T.time, Sum(X.units) As RunUnits
FROM table As T INNER JOIN table As X
ON X.time <= T.time
GROUP BY T.time

 

[raskew via AccessMonster.com]

Alex,

Try a search on 'Running Totals'. There are pages of examples.

Bob

Got a table look like this:

[quoted text clipped - 14 lines]

it must be simple but i have no clue how

This kind of query can be represented in the query design
grid:

SELECT T.time,
(SELECT Sum(X.units)
FROM table As X
WHERE X.time <= T.time) As RunUnits
FROM table As T

OTOH, this kind should be more efficient, especially if the
time field is indexed, but it can only be done in SQL view:

SELECT T.time, Sum(X.units) As RunUnits
FROM table As T INNER JOIN table As X
ON X.time <= T.time
GROUP BY T.time

 

[alex]

Thank you all

Everthing works fine!

Alex,

Try a search on 'Running Totals'. There are pages of examples.

Bob

[quoted text clipped - 14 lines]

it must be simple but i have no clue how

This kind of query can be represented in the query design
grid:

SELECT T.time,
(SELECT Sum(X.units)
FROM table As X
WHERE X.time <= T.time) As RunUnits
FROM table As T

OTOH, this kind should be more efficient, especially if the
time field is indexed, but it can only be done in SQL view:

SELECT T.time, Sum(X.units) As RunUnits
FROM table As T INNER JOIN table As X
ON X.time <= T.time
GROUP BY T.time