Database starting Point

[harix]

I want to create a db that tracks a group (25) of limited partners' share value. Each partner contributes a certain amount (original balance), receives interest payments/income payments (interest/income can be paid back to contributor or re-deposited as additional contributions), contributes additional varying amounts (deposits) and their current balance may go into the negative if their share of expenses exceed their current balance (the interest would be calculated on the negative balance and effect their current balance).

I am not worried about the account going into the negative because the share is backed by income property.

Where is my starting point? Should I set up an Excel spreadsheet for each partner so I can streamline the calculations of current balance etc. - it requires a lot of calculations based upon the previuos transaction [current balance, accrued daily interest, etc.]. I know how to do all these in Excel or Lotus but, a database may be easier to do data entry. I tried to do this with Dlookup and tables strictly in Access but failed.

I can do complex database design but this idea has me stymied.

I would appreciate some advice as to the basic structure, once I know the best way to set things up I can do the design easy enough, I just need a starting point. Since I have 25 different accounts that require calculations based upon previous balances, interest accrual etc. I am still at a loss as to how to proceed - spreadsheets or tables for each client??? then how do I get the forms to call different tables or spreadsheets HELP!!!!

 

[Pat Hartman]

You need at least three tables. One to hold static Partner info and another to hold transaction info. You will also need a lookup table to hold the various valid transaction codes. The Transaction table will be basically only a few columns

TranId (an autonumber) (primary key)
PartnerID (foreign key to Partner table)
TranCD (initial investment, additional capital, Interest payment, adjustment, etc.) (foreign key to transaction code table)
TranDate
TranAmt
UpdateDateTime (automatically populate this with Now() as each record is added or changed)

TranAmt should be entered as positive or negative depending on how you want it to affect the total. Things to be added are entered as positive. Things to be subtracted are entered as negative.

You'll need various append queries that calculate interest due or paid and post transactions. You should be able to just sum all the transaction records for each partner to get a balance at a point in time.

Since you're dealing with people's money and they get kind of funny about that, you'll need audit trails and controls to prevent post-dated entries and changes to data already entered. You'll need stringent rules on how you handle adjustments and you may want to have an additional table where you store the TranID as a foreign key so that you can record documentation relating to why an adjustment was made.