Querying for records with parameters depending upon previous dates

My table are in the form of :

Id, Date, Open,High,Low,Close,VOlume,OI

I am using MS Access, and I need to query like this:

Select those dates(D), where Close on D-2> D-3 and D-1>D-2

So, how do I form a query, with this? I开发者_如何学运维n general, you can think of it as a query with its parameters on previous records.

Soham

---

SELECT
  [Today].*
FROM
  (
    (
      MyTable AS [Today]
    INNER JOIN
      MyTable AS [TodayMinus1]
        ON  [TodayMinus1].Date = DATEADD("d", -1, [Today].Date)
        AND [TodayMinus1].ID   = [Today].ID
    )
  INNER JOIN
    MyTable AS [TodayMinus2]
      ON  [TodayMinus2].Date = DATEADD("d", -2, [Today].Date)
      AND [TodayMinus2].ID   = [Today].ID
  )
INNER JOIN
  MyTable AS [TodayMinus3]
    ON  [TodayMinus3].Date = DATEADD("d", -3, [Today].Date)
    AND [TodayMinus3].ID   = [Today].ID
WHERE
      [TodayMinus1].Close > [TodayMinus2].Close
  AND [TodayMinus2].Close > [TodayMinus3].Close

EDIT Note to elaborate on the use of three joins.

Systems like SAS operate as explicit loops where you are able to base a calculation on the values or results obtained from previous itterations of the loop.

SQL, however, is expressed as Sets rather than loops, and then the optimiser estimates the most algorithmically efficient way to accomplish that logic. This set based expression, however, traditionally means that you can't say "three records ago" as the set doesn't have an explicit order, or an order it is processed in (parallelism may mean it's processed in chuncks, index may mean it's processed in different orders, etc, etc).

This means that you need a set based mechanism for obtaining the records you want to compare. In this case, if you want to compare "today" with "yesterday", each on of those is a set which you join together before comparing. You have a total of 4 different days, so 4 different sets to join together for comparison. In a harsh sense, that's just how a relational database's set based expression works...

ANSI-SQL does now, however, include windowing functions such as LAG that allow a set based notation for what you desire. It is not yet widely implemented for a variety of reasons. As ACCESS is a light-weight database (compared with MySQL, SQL Server, Oracle, etc) I wouldn't expect leading edge functionality.