Need Running Balance In Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
Thanks for your help Ken

Ken Sheridan said:
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
Click to expand...
 
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;
 
Back
Top