Running Sum

[Guest]

Hey there,

I know there are a lot of posts on Running Sum's however, I just can't seem
to get it.

I've tried the DSum formula before and it works fine in most cases except
when I have mulitple transactions happening on the same day. So I thought
I'd give sub-queries a try. I have four main fields needed for this query

Account - a specific account
TransDate - The date the transaction occured
TransID - AutoNumber generated for each transaction
Balance - total amount of the transaction

The thing that's kind of messing me up is adding transactions for proir
periods. They throw my TransID numbers out of order when I'm trying to do
this kind of calculation: For example, I could have the following Data Trend:

Account TransDate TransID Balance
1 02/16/06 210 500
1 02/16/06 211 50
1 02/17/06 209 -100
1 02/18/06 212 50
2 02/16/06 213 300
2 02/16/06 214 10
2 02/17/06 207 -200
2 02/18/06 208 40

Ideally, I'd love to have a running sum sorted by account, then by
TransDate, then by TransID

Here is my attempt at a sub-query:

SELECT A.Account,
A.TransDate,
A.TransID,
A.Balance,
(SELECT Sum(Balance)
FROM qryAccountSummary
WHERE (qryAccountSummary.Account = A.Account) AND
(qryAccountSummary.TransDate <= A.TransDate) AND
(qryAccountSummary.TransID <= A.TransID)) AS RunningSum
FROM qryAccountSummary AS A
ORDER BY A.Account, A.TransDate, A.TransID;

Thanks for your assistance!! I'm just not sure what I'm doing wrong. I've
tried all sorts of combinations, but I just can't get it to work.

 

[Marshall Barton]

I know there are a lot of posts on Running Sum's however, I just can't seem
to get it.

I've tried the DSum formula before and it works fine in most cases except
when I have mulitple transactions happening on the same day. So I thought
I'd give sub-queries a try. I have four main fields needed for this query

Account - a specific account
TransDate - The date the transaction occured
TransID - AutoNumber generated for each transaction
Balance - total amount of the transaction

The thing that's kind of messing me up is adding transactions for proir
periods. They throw my TransID numbers out of order when I'm trying to do
this kind of calculation: For example, I could have the following Data Trend:

Account TransDate TransID Balance
1 02/16/06 210 500
1 02/16/06 211 50
1 02/17/06 209 -100
1 02/18/06 212 50
2 02/16/06 213 300
2 02/16/06 214 10
2 02/17/06 207 -200
2 02/18/06 208 40

Ideally, I'd love to have a running sum sorted by account, then by
TransDate, then by TransID

Here is my attempt at a sub-query:

SELECT A.Account,
A.TransDate,
A.TransID,
A.Balance,
(SELECT Sum(Balance)
FROM qryAccountSummary
WHERE (qryAccountSummary.Account = A.Account) AND
(qryAccountSummary.TransDate <= A.TransDate) AND
(qryAccountSummary.TransID <= A.TransID)) AS RunningSum
FROM qryAccountSummary AS A
ORDER BY A.Account, A.TransDate, A.TransID;

You need to break the multi-field combinations down further:

.. . . WHERE (qryAccountSummary.Account = A.Account) AND
((qryAccountSummary.TransDate < A.TransDate) OR
(qryAccountSummary.TransDate = A.TransDate AND
qryAccountSummary.TransID <= A.TransID))
) AS RunningSum
.. . .

 

[Guest]

Thanks for your help Marshall,

Unfortunately I haven't gotten around to testing this yet because every time
I try to run the query, Access shuts down on me and crashes. I've tried to
decompile the database but I'm not having any luck.

Is there something I'm doing that is causing this?? Access 2003 (which I'm
starting to dislike) and Windows XP.

 

[Guest]

Hey Marshall,

I found that your query worked wonderfully. Thank you.

The problem that I was having was that I was running this query off of
another query. The first query, qryAccountSummary, had some grouping and
summing in it and for some reason, Access didn't like that. Access was
crashing each time I tried to run the second query. Once I took the grouping
out of the original query, it seemed to work okay.

Not sure why this was a problem, I couldn't find it on any of the
troubleshooting web sites, if you have any info regarding this, it would be
interesting to know.

Thanks again for your continued support!!

 

[Marshall Barton]

Hey Marshall,

I found that your query worked wonderfully. Thank you.

The problem that I was having was that I was running this query off of
another query. The first query, qryAccountSummary, had some grouping and
summing in it and for some reason, Access didn't like that. Access was
crashing each time I tried to run the second query. Once I took the grouping
out of the original query, it seemed to work okay.

Not sure why this was a problem, I couldn't find it on any of the
troubleshooting web sites, if you have any info regarding this, it would be
interesting to know.

Thanks again for your continued support!!

Glad to help.

I can't help explain the crashing, but you did a nice job of
tracking it down. I have heard of complex(?) queries
causing trouble, but I have never experienced it.