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
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