Running Sum Until

  • Hi all,
    I am trying to make a query that returns on what date a security in the current inventory entered a portfolio in Access.
    The data is stored as trades which are summed to give the current inventory.
    My first approach was to take to current inventory and try to back out the trades until the position would be zero, but I could not figure out a way to do a running sum Unitl that would be completed in reverse chronology in access.
    Any help would be appreciated
    Thanks,
    JP

  • Re: Running Sum Until


    I'm pretty sure this can be done in Oracle and possibly SQL Server too. I don't believe Access has the ability. If the tables in Access are actually linked tables to an Oracle or SQL Server back-end then we could use native SQL via a pass-through query to return the results you're looking for. In Oracle, the following would produce a running total of a field named TRADE_AMOUNT (titled RUNNING_TOTAL) in the reverse order of the time the trades occurred:


    SELECT
    [indent]TRADE_ID,
    TRADE_DATE,
    SECURITY_ID,
    TRADE_AMOUNT,
    TRADE_AMOUNT + LAG(TRADE_AMOUNT, 1, 0) OVER (ORDER BY TRADE_DATE DESC) AS RUNNING_TOTAL[/indent]
    FROM
    [indent]TRADES[/indent]