DSUM question

[John]

Trying to make a running sum query using DSUM. I've looked at the sample from
rogersaccesslibrary.com and searched google. My problem is that when I try to
Group By using AcctNumber, I get the same thing in all records, which is the
overall total of all records, rather than running sum for each AcctNumber.
AcctNumber is text, not numeric.

SELECT tblTransactions.TransActID, tblTransactions.AcctNumber,
tblTransactions.Amount, tblTransactions.Description,
DSum("Amount","tblTransactions","[AcctNumber]='" & [AcctNumber] & "'" And "
TransactID <=" & [tblTransactions].[TransactID]) AS RunningBalance
FROM tblTransactions
ORDER BY tblTransactions.TransActID;


When I don't try to Group By AcctNumber, it works as expected, in other words,
the running sum is working on TransactID:

SELECT tblTransactions.TransActID, tblTransactions.AcctNumber,
tblTransactions.Amount, tblTransactions.Description,
DSum("Amount","tblTransactions"," TransactID <=" &
[tblTransactions].[TransactID]) AS RunningBalance
FROM tblTransactions
ORDER BY tblTransactions.TransActID;

How can I get it to group on AcctNumber (which is text)?

Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't include the TransActID, Amount, nor Description in the SELECT
clause; those data apply to individual rows. Using GROUP BY means you
want a summary of the rows (records), not individual rows (records),
which means you can only have columns (fields) in the SELECT clause that
can be summarized. E.g., the TransActIDs can't be summarized 'cuz there
is only one for each row (I'm guessing). The same AcctNumber can appear
in many rows and, therefore, it can be summarized.

SELECT AcctNumber,
DSum("Amount","tblTransactions"," TransactID <=" &
[tblTransactions].[TransactID]) AS RunningBalance
FROM tblTransactions
GROUP BY AcctNumber

Another, sometimes faster, way to do running sums is like this:

SELECT T1.AcctNumber, SUM(T1.Amount) AS RunningBalance
FROM tblTransactions As T1 LEFT JOIN tblTransactions As T2 ON
T1.AcctNumber = T2.AcctNumber
WHERE T1.TransActID <= T2.TransActID
GROUP BY T1.AcctNumber

Use this type of running sum query when the data set gets larger than
100,000 rows.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR9TgcIechKqOuFEgEQIPwACgqCYETtvnWMZYTEWOSnR3OWa0DVMAnjvD
Ppa6V5rpSKZlIGX50rMM8APb
=KDri
-----END PGP SIGNATURE-----

 

[johnboy7676]

Thanks for the reply. When I try the first one, I get:
You tried to execute a query that does not include the specified
expression 'DSum("Amount","tblTransactions","TransactID<=" & _
[tblTransactions].[TransactID])' as part of an aggregate function.

The second one works, giving a result of one total for each
AcctNumber, but thats not the result I wanted.

I apologize, I wasn't clear on what I'm trying to do.

I want a result of: all records returned, but with an additional
'runningBalance' column added:

AcctNumber Amount RunningBalance
1A $2 $2 (sum of 1A so far)
3B $6 $6 (sum of 3B so far)
4A $1 $1 (sum of 4A so far)
1A $2 $4 (sum of 1A so far)
3B $5 $11(sum of 3B so far)

There are other fields such as ReceiptNumber, Description,
TransactDate, that also need to be included in the query, left out
above trying to shorten this post.

TransactDate is Date/Time, but this was imported, and the original
data was Date only, only what records have been added since are both
Date and Time, if it matters. TransactID is autonumber.

Every way I try, I get result of:

AcctNumber Amount RunningBalance
1A $2 $2
3B $6 $8
4A $1 $9
1A $2 $11
3B $5 $16

Trying to get one query, which will then be used for the basis of a
subform which will only show the pertinent AcctNumber.

Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Okey doke, try this:

SELECT T1.TransActID, T1.TransActDate, T1.AcctNumber, T1.Amount,
T1.Description,
SUM(T1.Amount) AS RunningBalance

FROM tblTransactions As T1 LEFT JOIN tblTransactions As T2 ON
T1.AcctNumber = T2.AcctNumber

WHERE T1.TransActID <= T2.TransActID

GROUP BY T1.TransActID, T1.TransActDate, T1.AcctNumber, T1.Amount,
T1.Description

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR9Xn5oechKqOuFEgEQIyQgCgmJvk2tb+eKb89fbNX9JRgNacfDMAn3ba
PXlaaC3Fv/MebAX4Kwbt8Dj3
=3xEk
-----END PGP SIGNATURE-----

Thanks for the reply. When I try the first one, I get:
You tried to execute a query that does not include the specified
expression 'DSum("Amount","tblTransactions","TransactID<=" & _
[tblTransactions].[TransactID])' as part of an aggregate function.

The second one works, giving a result of one total for each
AcctNumber, but thats not the result I wanted.

I apologize, I wasn't clear on what I'm trying to do.

I want a result of: all records returned, but with an additional
'runningBalance' column added:

AcctNumber Amount RunningBalance
1A $2 $2 (sum of 1A so far)
3B $6 $6 (sum of 3B so far)
4A $1 $1 (sum of 4A so far)
1A $2 $4 (sum of 1A so far)
3B $5 $11(sum of 3B so far)

There are other fields such as ReceiptNumber, Description,
TransactDate, that also need to be included in the query, left out
above trying to shorten this post.

TransactDate is Date/Time, but this was imported, and the original
data was Date only, only what records have been added since are both
Date and Time, if it matters. TransactID is autonumber.

Every way I try, I get result of:

AcctNumber Amount RunningBalance
1A $2 $2
3B $6 $8
4A $1 $9
1A $2 $11
3B $5 $16

Trying to get one query, which will then be used for the basis of a
subform which will only show the pertinent AcctNumber.

Thanks

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't include the TransActID, Amount, nor Description in the SELECT
clause; those data apply to individual rows. Using GROUP BY means you
want a summary of the rows (records), not individual rows (records),
which means you can only have columns (fields) in the SELECT clause that
can be summarized. E.g., the TransActIDs can't be summarized 'cuz there
is only one for each row (I'm guessing). The same AcctNumber can appear
in many rows and, therefore, it can be summarized.

SELECT AcctNumber,
DSum("Amount","tblTransactions"," TransactID <=" &
[tblTransactions].[TransactID]) AS RunningBalance
FROM tblTransactions
GROUP BY AcctNumber

Another, sometimes faster, way to do running sums is like this:

SELECT T1.AcctNumber, SUM(T1.Amount) AS RunningBalance
FROM tblTransactions As T1 LEFT JOIN tblTransactions As T2 ON
T1.AcctNumber = T2.AcctNumber
WHERE T1.TransActID <= T2.TransActID
GROUP BY T1.AcctNumber

Use this type of running sum query when the data set gets larger than
100,000 rows.