query to calc running total

[Guest]

Problem#1
This query is supposed to generate a running total on a savings account. The
RUNNING TOTAL column attempts it by using dates, the BALANCE attempts it by
using TRANSNUM (basically a sequential number attached to the record).

RUNNING TOTAL generates an erroneous balance, and BALANCE generates an #ERROR

SELECT tblSavingsFrancine.EMPID, tblSavingsFrancine.TransDate,
tblSavingsFrancine.Transaction, DatePart("m",[TransDate]) AS Amonth,
DatePart("d",[transdate]) AS ADay,
FormatCurrency(DSum("[transaction]","tblsavings","DatePart('m',
[transdate])<=" & [AMonth] & " And DatePart('d', [transdate])<=" & [Aday] &
""),2) AS [Running Total],
FormatCurrency(nz(DSum([transaction],"tblExpenseAccount","[TransNum] <= " &
[TransNum]),0),-1,0,0,0) AS Balance, tblSavingsFrancine.TransClass
FROM tblSavingsFrancine
ORDER BY tblSavingsFrancine.EMPID, DatePart("m",[TransDate]),
DatePart("d",[transdate]);

I have to use this query to build another query to append "interest" records
to the same savings table and from there I do a running sum report.

Thanks..Juanita

 

[MGFoster]

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

You could use the VBA functions Month() and Day() instead of DatePart().

Month(TransDate) As Amonth
Day(TransDate) As Aday

If the TransDate column has dates in multiple years your DSum()
functions will probably return totals that you don't expect, because
you'll get data from more than one year.

Perhaps this qry will do what you want:

SELECT EMPID, TransDate, Transaction,
DSum("Transaction","tblSavings","TransDate <= " & S.TransDate) As
RunningTotal,
DSum("Transaction", "tblExpenseAccount","TransNum <= " & S.TransNum) As
Balance,
TransClass
FROM tblSavingsFrancine AS S
ORDER BY EMPID, TransDate

Use the alias "S" to refer to the main query's table in the Balance
expression.

For clarity's sake I left out the call to the FormatCurrency() function.
If it is just formatting the value as a Currency type you can use
Format(value, "Currency"). If the Transaction column is a Currency data
type the query result should show it in currency format. You don't need
Nz() inside a Sum()/DSum() function, 'cuz Sum(null) and DSum(Null,etc.)
will return zero.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQXrWsIechKqOuFEgEQJCFwCg4bvfgbFRDLtcJcLTmfUfAsCAVyUAnRdK
3C+e9s2d0SgE0eXGfDL0aTSf
=WFkr
-----END PGP SIGNATURE-----