managing data

  • Thread starter Thread starter shank
  • Start date Start date
S

shank

I need to log and manage royalties for songs. I will need to do quarter
reports and draw off a prepaid balance. I know how to do this using a
"running balance" method where the current calculations would be based on
the starting balance that could be years old. Now I'm thinking that may not
be the best way. I'm thinking I should reconcile the balances every period
like Quickbooks would do. Then each report would be based on data from the
last report.

Assuming I choose to reconcile, how do I manage the data? Should I create a
new column for every period? That doesn't seem right. How do I keep a
starting blance and subsequent quarterly balances in a database? It seems
like I could create a new table and do a one-to-many relationship but to
keep integrity I think I would have to associate on too many columns. That
lends to mistakes.

Has anyone done something similar? How do you manage the reconciliation from
quarter to quarter?

thanks!
 
Dear Shank:

Pretty good questions!

I recommend you keep a BalanceForward table, keyed by account and
DateBalanced. If you want a statement as of any date, it can start with the
most recent (MAXIMUM()) date for which you have a BalanceForward for each
account prior to the date of the statement. Then the detail for that
statement would be everything between the date of the BalanceForward and the
date of the statement.

Whenever a new account is opened, be sure to create a balance forward
record, probably with a 0 balance.

You have to have a convention. I prefer that a BalanceForward record be as
of the beginning of the date in question. All detail for that same date is
then subsequent to the BalanceForward amount.

Once a BalanceForward is created, I recommend you prohibit any posting to
that account prior to that date. The previous statement is "closed" and
cannot be altered. Any alterations for errors in the account must be posted
to the current "open" period.

This is an important exception to the rule that derived values not be
stored. It becomes impractical to, in effect, recalculate the account's
balance going back several years. Some compromise to allow better
performance may be essential. However, unless there is a "closing" of
previous statement periods, that is, unless it is agreed such statements are
unalterable history, you should not do this. Having a calculated value
store and then allowing it to change is a real mess, to be avoided. If you
cannot "close" statements until they are several months old, then keep
BalanceForward records only from the date of "closed statements" backward.
As long as the data is subject to change, do not store calculated values.
Instead, they must be recalculated live every time they are needed.

If is is not practical to "close" old statements, then advise the boss he
needs to be prepared to lay out a large amount of money for computer
equipment and software. Start by frightening him with a 6 figure estimate,
though it may be possible to implement with a 5 figure expenditure for a
while. With enough computing power, you could keep statements open for many
years. You'd better figure on migrating to SQL Server and learning to work
with that.

All of this depends significantly on the size of the data being processed.
Multiply the number of accounts with the number of open items per month.
Just how big would the database grow?

Tom Ellison
 
WOW! I appreciate your response. More issues have come to light with your
input. "Closing" a statement and following the rules will be the biggest
issue. We have bad habits of going back and tweaking things and that would
just make a mess out of everything. I like the BalanceForward idea and I can
see how critical it is to not muck with previous data. This could be a pain!
I also use SQL. But I'm not going there until I can get the idea straight in
Access.
thanks!
 
Back
Top