I'm attempting to build an Access database to manage the investments of our trading pool of less than 10 members. It was easy for the first half of the year in Excel when we didn't change the percentage of member cash investment but this month a few people are adding additional funds and 2 more people are joining. It gets complicated because there can be several trades during the day and new funds are entering the account while there are open trades in progress. New funds should only be part of new trades opened after the deposit date & time.
I'm stuck at trying to calculate the member column value in the query qry_fund_value_at_open_time_with_profit.
Link to example db db1.zip
The member value begins when their first deposit hits the trading account where you see [Type]=balance, [MemberID]=1,2,3...etc. 100% of the dollar value in the Profit cell is applied to that member ID.
I have the parts that are in bold but it's the values I need from past events that is stumping me. I feel like a dog chasing it's tail. I made the calculations manually in Excel so I hope the image below helps clear up how I'm doing the calculations. All of the data need is there I just can't get it to work in Access.
IF MemberID is Null then ((member dollar value at open time /[OpenTimeFundValue])*[Profit])+member value from the previous ClosedTime ELSE add [Profit] to member
IF MemberID is Null then ((member dollar value at open time/fund dollar value at open time)*profit)+member dollar value from previous closed time
((10,156.82/13,116.82)*30.56)+10,158.37=10,182.03
Let me know if anything needs additional clarification. I've been working on this one query for a week now and still haven't solved it.
Phil