Carrying Balance Forward

[wawa]

I am designing a database for a client that needs the
stock balance of items for a particular date. The items
supplied minus items issued gives the the balance for that
date. This balance now is to serve as the balance carried
forward for the next transaction. It is to be added to the
items supplied to get items available for issue. My
problem is how to make the balance carried forward to
reflect in the new transaction.

Please, Help!!!

 

[Joe Fallon]

These types of things are fairly tricky.
I didn't tackle these things until I had built a number of databases.

Here is one idea that may put you on the right track:

I once built a database which was a rewards system with points
that could be cashed in. It can be done with three tables.
Employees, Points and EmployeePts which is a junction table used to resolve
the M-M relationship into two 1-M relationships.

The Points table has fields like:
PtsID, PtsDescription,Points
The points are for both accruing and redeeming.
E.g. 3 Points for perfect attendence for the month.
-500 points for a redeemed Vacation Day.

The Employee table has the usual fields including:
EmployeeID, Fname, Lname, StartPts, PtsDate, etc.

The EmployeePts table has:
EmployeeID, PtsID, PointsDate (triple PK)
This allows you to assign the same points on different days of the year.
(Recurring awards).

Add up all points for the previous year into a new StartPts in the Employee
table.
Then let all the transactions occur in the EmployeePts table.
To find the balance at any time it is just a matter of summing the points
for the current year in the EmployeePts table and adding the StartPts.

Use forms to assign the points.
Don't allow a redemption unless there are enough points accrued.