Lag function in Microsoft Access (Window Function)

Question

How can I use Window function in Microsoft Access. Here is a snippet of my query.

SELECT [OutTest]![CoSerNum] AS CoSerNum, lag([OutTest]![CoSerNum] ,1) over(order by [OutTest]![CoSerNum] ) 
FROM OutTest
WHERE (((OutTest.SO)=[Enter SO]));

Can someone please help me out with the query in MS Access.

Answer 1

Access does not support window functions. Here, you can emulate it with a correlated subquery:

SELECT 
    ot.[CoSerNum] AS CoSerNum, 
    (SELECT MAX(ot.[CoSerNum]) FROM OutTest ot1 WHERE ot1.[CoSerNum] < ot.[CoSerNum]) AS lagCoSerNum  
FROM OutTest ot
WHERE ot.SO = [Enter SO];