Results 1 to 2 of 2
  1. #1
    360 is offline Novice
    Windows 7 Access 2000
    Join Date
    Jul 2010
    Posts
    1

    Financial query based on multiple event times


    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

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    I'm a little unclear about your spreadsheet and hence your table structure. For example, the tickets 1747064854, 1747064033 and 1747065500 appear to be tied to memberID #1 since profit amounts are impacting that member's balance but there is no value in the MEMBERID for these tickets. If the tickets are related to that member the memberID cannot be left blank. This implies that every ticket needs a memberID which, in turn, would indicate that your tbl_mt4_data is the real transaction table. You also have 2 price fields in tbl_mt4_data, this is an example of repeating groups which might indicate that the table is not normalized. Also, what are the SL, TP fields? Is profit a calculated field? If so, the value should not be stored in a table, but calculated on when needed.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 04-08-2010, 12:22 PM
  2. Query: How many times does a value exist?
    By hognabbt in forum Queries
    Replies: 1
    Last Post: 01-10-2010, 01:33 PM
  3. Send multiple e-mails through Outlook based on query
    By dataphile in forum Programming
    Replies: 3
    Last Post: 12-30-2009, 12:04 AM
  4. Replies: 5
    Last Post: 12-10-2009, 10:33 PM
  5. Replies: 3
    Last Post: 11-02-2009, 04:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums