Compute a Running Balance

To apply what you have learned, the following example is a known problem for many users in all sectors of business. There are times when a user needs to compute a running balance based on a single column of numbers. This example uses a typical bank account, because most people can associate to it. The technique that is conveyed can be modified to pertain to invoices, mileage, weights, or anything else in your business model that would require a running balance. To start, suppose that there exists a table that stores bank account transactions, which may resemble Figure 11-5.

■ tb ¡Transact! on

: Table

TnT*

TransaclionlD

Payee

Purpose

TransactionDate

Amount

i

Ccnglcmo Corp

Salary

1/1/2003

(2.000.00

2

fvlega-low Mart

Household

1/3/2003

($ 157.40)

3

Electric Co

Electricity

1/5/2003

($94.31)

4 Water Co

Water

1/6/2003

($34.55)

5 Insurance Co

Insurance

1 /8/2003

($245.04)

6 Supermarket

Groceries

1 /'10/2003

($132 09)

IT

(AutoNurnber)

50. GO

Record: H 1

11 1 ► j H |m| of 6

Figure 11-5

Figure 11-5

In the screenshot, there is an example data table, in which the Payee, Purpose, and Transaction Date fields are self-explanatory. For clarification, the Amount field retains the dollar figure for each transaction, where positive values are deposits and negative values are withdrawals. (Your paper checkbook may have different columns for each, but the same principle would apply.)

When new developers are faced with the running balance task, they initially try to solve the problem within the table itself. They add a new column to the table, call it Balance, and then attempt to populate the data with a query. This technique works, but only for the sort order in which the query was executed.

For example, suppose that the developer decided to create the running balance based on Transaction Date. Figure 11-6 displays the results of the query technique.

■ ib ¡Transact! on : Taille

H. □ ! x I

Transaction^ Payee

Purpose

TransactionDate

Amount

Balance |

J Concjlormo Corp

Salary

1/1/2003

Î2.GU0.0G

$2.000.00

2 Mega-low M?ri

Household

1/3/2003

"($157.48)

$1.342.52

_3 Electric Co

Electricity

1/5/2003

($94.31)

$1,748.21

4 Water Co

Water

1/6/2003

($34.56)

$1,713.65

5 Insurance Co

Insurance

1/a/2003

($245.04)

$1.468.61

6 Supermarket

Groceries

1/10/2003

($132.09)

$1.336.52

T

(AutoNurnber)

$0.00

$0.00

Record: l«j < (| 1 » 1 « l»*l of 6

Figure 11-6

Figure 11-6

This would appear to be a logical way to do it, but just for the sake of confusion, suppose that the user decided to sort the data by the Purpose column, and/or filter the data based on the Payee. (For the filter example, it is assumed that there are multiple months of transaction data.) If the user were to perform the sort, then the data, including the balance field, would be sorted. It would look like Figure 11-7.

IbiTransaclion

: Table

TransactionlD

Payee

Purpose

TransactionDate

Amount

Balance |

3

Electric Co

SSwijiEiS!

1/5/2003

($94 31J

$1,748.21

6

Supermarket

Groceries

1/10/2003

_($132.09)

$1,336.52

2

Mega-low Marl

Household

1/3/2003

($157.48)

$1,342.52

5

insurance Co

insurance

1/3/2003

^$245.04)

$1,468.61

1

Conglomo Corp

Salary

1/1/2003

$2,000~ÔÔl

$2,000.00

4

Water Co

Water

i/B/2003

($34 56) :

$1,713 65

T

(AutoNumber)

$0.00

$0.00

Record: w| jp

l ► | h !►*! of 6

0 0

Post a comment