Tracking current balance (1 Viewer)

[atol]

Hello,
I originally put this under "Query" category, but was not sure where actually it belongs... so I aploogize for posting this second time.

I need a little bit of guidance with the following.
Here is the scenario: two (or many) investors invest in bonds; usually the typical transactions could be buy, sell, principal repayment, or interest payment.
I consider the following tables:

tblInvestor
PK InvestorID
Name, etc

tblBond
PK BondID
Issuer
Coupon
Issue Date
Etc…

tblInvestorBond (this is a junction table since one investor can invest in many bond, and one bond can be held in many accounts)
PK InvestorBondID
(I do not consider any other attributes here)

tblTransactionHistory (This is important to keep track of any historical transactions, and also to have a running balance of the current positions for each investment)
PK TransactionHistoryID
FK InvestorBondID (from above)
TradeDate
SettlDate
FaceAmount
Price
SettlAmount
TransactionType (buy, sell, intetest… - from a lookup table)

First I want to make sure if such relationship among tables make sense. Second, I would like to have on a form, the current holding for every investor. Let’s say, I look up for a certain investor and I want to see what is its current position for a specific bond. And when I enter a transaction, I also want to see the position changed (for example, if there is a principal prepayment, I want to see the position decrease since that repayment would decrease the holding). How do I do that? Would that be with a query or a VBA code? As a matter of fact my Access knowledge is limited (obviously), and any ideas are greatly appreciated.
Rgds.
Atol

 

[ajetrumpet]

Atol,

This is a tough question, kind of like your last post. Did you get all of that worked out?? Anyway...

I would say you're looking at writing a few queries here that will hold quite a few expressions in each of them (Example - to query a current account value, you will have to SUM all of the (net) changes that affect the account (from each transaction on the account). I would suggest creating one main form that offers to search through records based on the information that is needed...

Example - **Search 1 (command button??) = Find a transaction.
**Search 2 = Find an investor
**Search 3 = Find a specific bond or other security holding (if this DB has other security info. listed in it)

I would say a great way to do this would be to create either one (or you could do many) subform(s) on your main form that appear(s) after a button is clicked and the criteria is entered for the search being performed. You could also create popup forms too if you wanted.

As far as any calculated info you're going to want to query, it might be a little complicated to write the expressions, but if your table structure is fine, it should be easy enough.

One question about the junction table...is there only one field in it?? If so, is the InvestorBondID an autonumber?? I ask because the BOND table really should have a field referencing the Investor's ID.

 

[GaryPanic]

Ok what you need is a history table

with bond info on it .
so if the bond is sold then - the full amount
increases and decreases would also be entered by a transaction as would purchases

now your bond will have a PK on it and a FK of the client
now its quite easy to runa qry to show the status of all transactions

100+
50 -
25
Total 125.00

this can be either report based or form based
now if form based you need to tweak your form coding slightly and the information when you load the form will not instant it will take a couple of seconds to load the qry and punch the result into your form

alternatively a runing total would be instant - however there are pro's and con's of doing this
also how many clients are we talking about - this sort of design is probably already out there with an off the shelf product - you could be reinventing the wheel on this one.

from memory banking/finance companies are not overly keen on Access - not due to its design but to its secuirty from external hacking -

But the real power users here would have more info on this ...
g

 

[atol]

Thanks guys. As always I appreciate your help!
I actually missed to add in my original post the two important foreign keys to the
tblInvestorBond (this is the junction table since one investor can invest in many bond, and one bond can be held by many investors)
PK InvestorBondID - this is the autonumber (actually all my primary keys are autonum's)
FK InvestorID
FK BondID

These three attributes are the only ones I will have here in order to reflect many-to-many, i.e. many investors can invest in many bonds, and vice versa.

That actually answers the question Ajtrumpet asked in his post. And just to add a word about my previous post for the weighted-average interest rates, that was a great idea, AJ you gave me, but since I am still on design stage, I am trying to put some pieces together and figure out if this bite (mean the project) is too big for me. But I will definitely let you know when I reach to that point.

Gary, about the table history - would the one I have out there work from design standpoint? It will have an autonumber for uniqueness of each transaction; and it will have (as also shown in my post) a foreign key that comes from tblInvestBond. This way I would ensure integrity among all four tables. So, I guess the history table would work out based on how it is related to the rest of the tables? Or I might be wrong… I do not plan to add any more attributes here though…

In terms of the form/subform - the way I visualize it, it would look like this: a main form with practically two search combo boxes - one to pick the investor, and second to pick the bond name (perhaps name and maturity date – for uniqueness). Once these two are picked, then perhaps I would have a command “search” button, which (by clicking on it) would open a subform where I can see all transactions, and somewhere I would also see the current balance (not sure where exactly). I am not sure how much of a headache would be to create such a search form. Even not sure if there is a code somewhere I can find.
Rgds!