splits investment transaction

[Anne]

I need an idea on how to split a transaction into two. I am recording
investments in stocks and bonds, and many times only part of the shares are
sold and I need to split the cost between the stock sold and the stock still
held.

For example
Teststock date 09/16/09 shares 1000 value 9616.96
needs to be split into two items
Teststock date 09/16/09 shares 500 value 4808.47
Teststock date 09/16/09 shares 500 value 4808.48

Any suggestions on how to do this?

 

[Anne]

Was remembered that I had a similiar problem before and I looked it up and I
did it by adding a table, where I add 2 lines items to the linked purchase
ID. Then I createdthe split below.

SELECT [24cboInvestmentsOpen].TransID, [24cboInvestmentsOpen].Transdate,
[24cboInvestmentsOpen].SymbolID, [24cboInvestmentsOpen].SharesPurch,
[24cboInvestmentsOpen].PurchAmount, TblSplitInv.PurchID, TblSplitInv.Count,
DCount("Count","tblSplitInv","[PurchID] =" & [TransID]) AS SplitCount,
IIf([splitcount]>1,Round([sharespurch]/[splitcount],3)) AS SplitShares,
IIf([splitcount]>1,Round([purchamount]/[splitcount],2)) AS SplitAmount,
[purchamount]-[splitamount] AS AmountLeft, IIf([splitamount]<>[amountleft]
And [count]=1,[splitamount],[amountleft]) AS NewAmt1
FROM 24cboInvestmentsOpen RIGHT JOIN TblSplitInv ON
[24cboInvestmentsOpen].TransID = TblSplitInv.PurchID;

Now I just need to run a delete and update query to update the info.