from runningtotal t1, runningtotal t2

As I understood, Rudy suggests using the same table twice with different aliases - as "t1" for calculation and as "t2" to have a sorted column to compare with

A problem could occour here because of Access' assumption functions to be deterministic and not having any side effects. If you'd have duplicate values on SumOfaNum Access would likely optimize away the function call and use the cached result instead. But your running sum wouldn't be updated then.
A workaround would be to additionally pass a truely unique value to "cheat" the optimizer.

...and back to the fabulous theta join.

i have a problem

playing with A's stored queries 'cos it's easier for experiments, the closest i can get to rudy's SQL is:

SELECT t1.Month, t1.aNum, sum(t2.aNum) AS runTot
FROM aTable AS t1, aTable AS t2
WHERE t1.Month >= t2.Month
GROUP BY t1.Month, t1.aNum;
...to me this looks pretty much equivalent to rudy's statement.

aTable:
myID, Month, aNum
1,1,1
2,1,1
3,1,1
4,1,1
5,2,1
6,2,1
..........etc so that each of months 1...6 have four rows with aNum=1

expected running total is:
4, 8, 12, 16, 20, 24 (delta = 4)

what i actually get returned is:
16, 32, 48, 64, 80, 96 (delta = 16) (??? sixteen ???? where's that from?)

why ">" in WHERE t1.Month >= t2.Month (it obviously makes sense, because "<" simply reverses the return 96, 80, 64....)

izy

...and switching from ">=" to ">" changes nothing. izy

izy, the reason your example returns "blown up" results is because in your join, each row is joining with 4 times as many rows as normal, because you are joining on the wrong column

each row should join with all "less than or equal" rows only once

it's a running total query, not a query to produce intermediate sums by month

it has to work on a primary (i.e. unique) key

try this instead --this produces

myId Month aNum runTot
1 1 1 1
2 1 1 2
3 1 1 3
4 1 1 4
5 2 1 5
6 2 1 6
7 2 1 7
8 2 1 8
9 3 1 9
10 3 1 10
11 3 1 11
12 3 1 12
13 4 1 13
14 4 1 14
15 4 1 15
16 4 1 16
17 5 1 17
18 5 1 18
19 5 1 19
20 5 1 20
21 6 1 21
22 6 1 22
23 6 1 23
24 6 1 24

rudy

Edit: changed table names back to izy's example -- i prefer to set up meaningful names when creating test cases, and i forget to change back after pasting)

For Rudy's statement to work, it requires a unique id column to be used, otherwise access will by default assume the "full join" statement and cross-link all corresponding rows (4 rows x 4 rows = that's where 16 comes from).

However, making two queries like

Query queSubtotals
SELECT t1.Month, Sum(t1.Num) AS SubTotal
FROM atable AS t1 INNER JOIN atable AS t2 ON t1.id = t2.id
WHERE (((t1.Month)>=[t2].[month]))
GROUP BY t1.Month;

Query queRunningTotal:
SELECT qT1.Month, Sum(qT1.SubTotal) AS RunningTotal
FROM queSubtotals AS qT2, queSubtotals AS qT1
WHERE (((qT1.Month)>=[qT2].[month]))
GROUP BY qT1.Month;

might be a solution - the second one gives correct reqults.

Ooops, posted simultaneously... Well, there you go

got it

thanks, izy

Hello All,

This issue is really the bain of my existance. I'm glad I found this thread, thugh hope someone could helop follow-up on it a bit. I'm trying the exact same thing with R937 (Rudy)'s code, and am getting the following error:

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

I am a total noob and have no idea how to revise the code below, can anyone help out?
Thanks again!