Lag Partition equivalent MS Access

ms-access

Solution

Working from the Oracle example and considering your query, you may find a subquery would suit. If not, a User Defined Function would be a possibility.

This returns the previous order date by product_id

SELECT Orders.ORDER_DATE, 
       Orders.PRODUCT_ID, 
       Orders.QTY, 
      (SELECT Top 1 o.Order_Date 
       FROM Orders o 
       WHERE o.Order_Date<Orders.Order_Date 
       AND o.Product_ID=Orders.Product_ID 
       ORDER BY o.Order_Date DESC) AS PreviousOrder
FROM Orders
ORDER BY Orders.ORDER_DATE, Orders.PRODUCT_ID

In the sample data, each product_id has a different date for each order, so a subquery with ORDER BY date alone is sufficient, however, Top queries in Access return matches, so if duplicates are a possibility, it is best to order by the relevant field and a unique id.

Problem

I am looking for equivalent of following Oracle query in MS Access. I know that Lag Over (Partition by ) function is not available in MS Access but looking for to acheive same functionality of below query using MS Access.. Appreciate any inputs ``` select t.*, to_char(date1,'yyyymm') month, lag(value1,1,0) over(partition by Id1,name1,office,product,overall order by date1) prev_val from test_prev t order by name1, office, overall, date1, product ```

Original source