## 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