Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Calculating a running balance

Posted on 2003-11-07
Medium Priority
1,129 Views
How do you calculate a running balance in MS ACCESS Form i.e  if I want to creat a running balance for customer's daily purchase on credit.
0
Question by:HANKNO
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 9

Accepted Solution

svenkarlsen earned 200 total points
ID: 9704559
if you have the customer account table with e.g. a record for each purchase/transaction:

Table: CustTrans
Fields: CustID, Transdate, TransAmmnt

And then you have another table which sums up the first:

Table: DailyBalance
Fields: CustID, BallDate, BallAmnt

Then you can run a query every day (e.g. at midnight):

INSERT INTO DailyBalance ( CustID, BallAmnt, Balldate )
SELECT CustTrans.CustID, Sum(CustTrans.TransAmnt) AS SumOfTransAmnt, Date() AS CurrDate
FROM CustTrans
WHERE (((CustTrans.Transdate)<Date()))
GROUP BY CustTrans.CustID;

Of course this is just a synopsis, - in the real world you must code the errorhandlers, think about exceptions, probably adapt the query so it can be run at any date (not just current). But it'll get you by for starters, - notice that it gets the Customer list from the trans-table: this means than any client who has made a transaction will be included automatically, - i.e. you don't have to add new clients to the DailyBallance table.

Regards,
Sven
0

LVL 39

Expert Comment

ID: 10025047
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: svenkarlsen {http:#9704559}

stevbe
EE Cleanup Volunteer
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month10 days, 18 hours left to enroll