Tracking date-specific calculation strings

[Daniel]

Thank you in advance for taking a look at this problem.

I run a marketing company that finds institutional buyers
for our money manager clients (we currently have six).
Each client pays us a percentage of their fee (asset
based) if we successfully introduce them to a client.
Each client pays us a different fee (negotiated), and
they usually decline over time (25% for the first 12
months, 15% for the next 12 months, etc).

I am building an Access 2000 database to keep track of
our future revenue stream. I have built a table with the
Account Information (date funded, amount of account,
asset-based fee to the manager), and another with the
Client Information (our agreed payout over time).

I have also created a querie that calculates our revenue
stream by year for each account ($ value of the account *
the manager's fee * our agreed payout). However, this
approach ignores when the account was actually funded and
begins to accrue revenue.

I need to now find a way to query the information so our
revenue stream begins on the month the account is funded,
then continues on our payment schedule (usually in 12
month increments) into the future.

I believe I need to create another querie, but do not
know how to do it. Any ideas you might have would be
very much appreciated.

Sincerely,

Daniel Weiss
AMD Capital LLC

 

[Jeff Boyce]

Daniel

From your description, I'm wondering why you aren't building this in a
spreadsheet?

If you want to continue in Access, a first step might be to identify all the
data elements you need to do the calculation(s). For example, if you want
to know a given future month's revenue, you'd probably need to have monthly
expected amt for each client. To get that, you'd need to have ... ?!

Good luck!

Jeff Boyce
<Access MVP>