Balance Column in Account History

[SAC]

I would like some help in figure how to have a customer history showing:

Date
Desc
Charge Amount
Payment Amount
Balance

It's the "running"balance amount column I'm not sure how to make happen.

Do I store that amount in the Customer table?
Do I store that amount in the transaction table?

Or do I store it at all?

Thanks for your help.

 

[Fred]

For 98% of applications, the balance should be (automatically) be calculated
at the time of viewing / printing, and not stored. I'd start with the guess
that you are in the 98%.

 

[Issac]

So wht will be

 

[Ken Sheridan]

As Fred says, compute the balance on the fly in a query, don't store it in a
column in a table. Here's a query on a table structured similarly to your
transaction table:

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

Note that the table includes an autonumber TransactionID column to uniquely
identify each row. This is necessary to differentiate between two
transactions on the same day by the same customer. Essentially the query
works by summing the credits per customer up to each transaction date in the
first query, and the debits similarly in the second subquery, subtracting the
latter from the former to give the balance at the time of each transaction.

Ken Sheridan
Stafford, England

 

[SAC]

I agree. Just don't know how.

 

[SAC]

Thanks. I appreciate it!

As Fred says, compute the balance on the fly in a query, don't store it
in a
column in a table. Here's a query on a table structured similarly to your
transaction table:

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

Note that the table includes an autonumber TransactionID column to
uniquely
identify each row. This is necessary to differentiate between two
transactions on the same day by the same customer. Essentially the query
works by summing the credits per customer up to each transaction date in
the
first query, and the debits similarly in the second subquery, subtracting
the
latter from the former to give the balance at the time of each
transaction.

Ken Sheridan
Stafford, England

 

[SAC]

This isn't quite what I need.

The balance should be a continuation from the prior record and then the
debit and credit applied for a new balance.

Foe example:

If I have a debit of 10.00 and no credit, the balance is 10.00.

However, if the next transaction on my account is a credit of 9.00 and no
debit, then the balance is 1.00

Thanks for your help.