Need Running Balance In Form

[Guest]

XP Pro, 2003

I am trying to develop a program for managing petty cash. I have a table
with Deposit and Payment fields.
I use a form based on the table to record deposits and payments. I want to
create a query and calculate a running balance (sum of deposits - sum of
payments), and base the form on the query so the user sees the balance of
funds when making payments.

I tried using Dsum, but I cannot figure it out -- I am not a programmer.

Thanks for any help

 

[Guest]

Ideally for this you should have a unique TransactionDateTime column in the
table, but mostly people just have the TransactionDate so you then need to
bring a unique TransactionID column into play. This can be an autonumber as
its values are arbitrary. An updateable query using the DSum function would
then be:

SELECT TransactionDate, Deposit, Payment,
DSUM("Deposit", "Transactions","TransactionDate <=
" & FORMAT(Transactions.TransactionDate,"\#mm/dd/yyyy\#") &
" AND (TransactionID <= " & Transactions.TransactionID &
" OR TransactionDate <> " &
FORMAT(Transactions.TransactionDate,"\#mm/dd/yyyy\#") & ")") -
DSUM("Payment", "Transactions","TransactionDate <=
" & FORMAT(Transactions.TransactionDate,"\#mm/dd/yyyy\#") &
" AND (TransactionID <= " & Transactions.TransactionID &
" OR TransactionDate <> " &
FORMAT(Transactions.TransactionDate,"\#mm/dd/yyyy\#")& ")")
AS Balance
FROM Transactions
ORDER BY TransactionDate, TransactionID;

You can also have a non-updateable query entirely in SQL, this time in date
descending order. This should perform faster than one using the DSum
function so is better where the data does not need to be edited, e.g. as a
report's RecordSource:

SELECT TransactionDate, Deposit, Payment,
(SELECT SUM(Deposit)
FROM Transactions AS T2
WHERE T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate)) -
(SELECT SUM(Payment)
FROM Transactions AS T2
WHERE T2.TransactionDate <= T1.TransactionDate
AND ( T2.TransactionID <= T1.TransactionID
OR T2.TransactionDate <> T1.TransactionDate))
AS Balance
FROM Transactions AS T1
ORDER BY TransactionDate DESC , TransactionID DESC;

Its very important that there should be no NULLs in the Deposit or Payment
columns, so in table design set their Required property to True (Yes) and
give them a DefaultValue property of 0 (zero). The TransactionDate column
must also be Required of course.

Ken Sheridan
Stafford, England

 

[Guest]

Thank you for your response

I am having a bit of trouble still' Here is query SQL:
SELECT tblPCashTransV1.TransactionID, tblPCashTransV1.Deposit,
tblPCashTransV1.TransactionDate, tblPCashTransV1.Payee,
tblPCashTransV1.Payment, tblPCashTransV1.Balance,
tblPCashTransV1.ReceiptNumber, TransactionDate, Deposit, Payment,
DSUM("Deposit","Transactions","TransactionDate <= " &
FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & " AND
(TransactionID <= " & tblPCashTransV1.TransactionID & " OR TransactionDate <>
" & FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") &
")")-DSUM("Payment","Transactions","TransactionDate <= " &
FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & " AND
(TransactionID <= " & tblPCashTransV1.TransactionID & " OR TransactionDate <>
" & FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & ")") AS Balance
Order BY TransactionDate, TransactionID
FROM tblPCashTransV1;

I get an error msg "that the Select statement has a reserved word or an
argument name that is misspelled or missing, or the punction is incorrect.

Can you spot what I have done wrong?

Thanks

 

[Guest]

The ORDER BY clause is in the wrong place. It must follow the FROM clause.
Also you have the TransactionDate, Deposit and Payment columns twice in the
SELECT clause, and you are including the Balance column in the SELECT clause
twice, once as the column computed by the subqueries, and once as a named
column; the former should be removed, so you'd end up with:

SELECT
tblPCashTransV1.TransactionID, tblPCashTransV1.Deposit,
tblPCashTransV1.TransactionDate, tblPCashTransV1.Payee,
tblPCashTransV1.Payment, tblPCashTransV1.ReceiptNumber
DSUM("Deposit","Transactions","TransactionDate <= " &
FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & " AND
(TransactionID <= " & tblPCashTransV1.TransactionID & " OR TransactionDate
<> " & FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") &
")")-
DSUM("Payment","Transactions","TransactionDate <= " &
FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & " AND
(TransactionID <= " & tblPCashTransV1.TransactionID & " OR TransactionDate
<> " & FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & ")")
AS Balance
FROM tblPCashTransV1
ORDER BY TransactionDate;

Ken Sheridan
Stafford, England

 

[Guest]

Thanks for your help Ken

The ORDER BY clause is in the wrong place. It must follow the FROM clause.
Also you have the TransactionDate, Deposit and Payment columns twice in the
SELECT clause, and you are including the Balance column in the SELECT clause
twice, once as the column computed by the subqueries, and once as a named
column; the former should be removed, so you'd end up with:

SELECT
tblPCashTransV1.TransactionID, tblPCashTransV1.Deposit,
tblPCashTransV1.TransactionDate, tblPCashTransV1.Payee,
tblPCashTransV1.Payment, tblPCashTransV1.ReceiptNumber
DSUM("Deposit","Transactions","TransactionDate <= " &
FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & " AND
(TransactionID <= " & tblPCashTransV1.TransactionID & " OR TransactionDate
<> " & FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") &
")")-
DSUM("Payment","Transactions","TransactionDate <= " &
FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & " AND
(TransactionID <= " & tblPCashTransV1.TransactionID & " OR TransactionDate
<> " & FORMAT(tblPCashTransV1.TransactionDate,"\#dd-mmm-yy\#") & ")")
AS Balance
FROM tblPCashTransV1
ORDER BY TransactionDate;

Ken Sheridan
Stafford, England

 

[Guest]

One further point you might like to consider. Its normal accounting practice
for credits each day to be shown before debits. If you wish to follow this
convention then extend the ORDER BY clause to:

ORDER BY TransactionDate, IIF(Deposit>Payment,0,1),TransactionID;