Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: Nested Queries and Self-Joins

    New to Access and the forum - glad to be here...

    Using Access 2010 - table summary at bottom.

    I’m trying to use daily historic stock price data (High, Low, Close) to calculate a simple metric: PriorDayAvgPrice = ((High + Low + Close) / 3). This must be calculated and stored for historic analytics and generated daily going forward for use in monitoring the next day’s activity. I plan to hold all price data and metrics in tblStockPrices.

    As you'd assume, PriorDayAvgPrice for use in monitoring TradeDate activity is calculated using the High, Low and Close from tblStockPrices for the prior TradeDate.

    Because TradeDates are not necessarily sequential calendar dates I have two columns, TradeDate and SourceDate, in tblDateRef to control which High, Low, Close data to use for a given TradeDate’s PriorDayAvgPrice calculation.

    Sounds straightforward but I’m really missing something!!! Tried numerous queries (Sub-query and Join) to get the correct High, Low and Close from tblStockPrices by obtaining SourceDate for the TradeDate in question but can only get High, Low and Close for TradeDate or nothing at all (let alone pass it to the query to do the calculation and update actions).

    Any help / guidance in how to get the correct High, Low and Close data into the query that will calculate PriorDayAvgPrice and update tblStockPrices would be greatly appreciated. I can then modify this historic oriented process to perform Inserts daily going forward.

    Many Thanks!


    tblStockPrices:
    Symbol…..…PK
    TradeDate…PK
    High
    Low
    Close
    PriorDayAvgPrice

    tblDateRef:
    TradeDate……PK
    SourceDate

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Welcome to the forum!

    Given the nature of PriorDayAvgPrice, there are some members here (myself included) who will advise you not to store it in the database, but to write a query to derive it whenever you need to see it. The reason being that you shouldn't store derived values, as you then need to worry about updating them if for some reason your source values change.

    Anyway.

    Could you post some sample sample data, along with the results that you want to see from them? I have some ideas of how to accomplish what you want, put it helps to have them illustrated.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    Thank you for helping!

    Understand the point on derived data but thought it best to store in this case since (a) historic results will not change (except in rare instance such as stock splits…) (b) the equation is cast in stone (c) the data will be used heavily in analysis of historic price action and (d) potentially for display in a graphical front-end.

    Will think through this again. Does the above impact the advice to not store?

    Sample data:


    Code:
    tblStockPrices				
    Symbol pk            AAA        AAA         AAA             AAA
    TradeDate pk         31-Jul     1-Aug       2-Aug           3-Aug
    High                     7          8           9            null
    Low                      5          6           7            null 
    Close                    6          7           8            null
    PriorDayAvgPrice        --          6           7              8
    Code:
    tblDateRef
    TradeDate pk       31-jul     1-aug     2-aug     3-aug
    SourceDate         30-jul     31-jul    1-aug     2-aug

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    In your case, you'd probably be better off storing the derived data and scheduling an update on a daily basis.

    I've just hammered a quick SELECT statement together based on your sample values:
    Code:
    SELECT
    tblStockPrices.Symbol
    , tblStockPrices.TradeDate
    , tblStockPrices.High
    , tblStockPrices.Low
    , tblStockPrices.Close
    , tblStockPrior.TradeDate AS PriorDay
    , ([tblStockPrior].[High]+[tblStockPrior].[Low]+[tblStockPrior].[Close])/3 AS PDA
    FROM
    (tblDateRef 
    INNER JOIN 
    tblStockPrices 
    ON 
    tblDateRef.TradeDate = tblStockPrices.TradeDate)
    INNER JOIN 
    tblStockPrices AS tblStockPrior 
    ON 
    (tblStockPrices.Symbol = tblStockPrior.Symbol) 
    AND 
    (tblDateRef.SourceDate = tblStockPrior.TradeDate);
    That should get you in the right direction. Please note that this will only work if the source date is the day before the trade date. If not, you will need to play around with the self-join and use the DATEADD function - have a play and see what happens.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Aug 2012
    Posts
    4
    Thank you !

    Data from the prior trade date (as driven by tblDateRef) is correctly returned for the calculation in your code (but you knew that!).

    Still resolving why mine did not work but now have a standard to work from - life is good!

    Cheers

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!

    Also, please post your original query string- I'm sure someone here will pick it apart for you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •