Available LEAD AND LAG Function in Ms Access

%3CLINGO-SUB%20id%3D%22lingo-sub-1401504%22%20slang%3D%22en-US%22%3EAvailable%20LEAD%20AND%20LAG%20Function%20in%20Ms%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1401504%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Dear%20All%3C%2FP%3E%3CP%3EIn%20SQL%20Server%20we%20just%20have%202%20function%20(LEAD%20%26amp%3B%20LAG)%20for%20calculating%20between%20a%20value%20in%20same%20column%20at%20different%20rows%20and%20i%20know%20about%20them.%3C%2FP%3E%3CP%3EIn%20one%20Project%20i%20have%20to%20working%20with%20ms%20Access%20and%20that%20function%20i%20really%20need%20to.%3C%2FP%3E%3CP%3EI%20used%20below%20query%20and%20it%20works%20fine%20but%20it%20is%20very%20slow%20%2C%20for%20around%20313000%20record%20it%20will%20take%2045%20minutes%20or%20more%20%2C%20but%20in%20SQL%20Server%20with%20that%20functions%20(LEAD%20%26amp%3B%20LAG)%20it%20will%20take%201%20sec.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20Query%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%3ESELECT%20MD.WONO%2C%20MD.TradeID%2C%20MD.StatusID%2C%20MD.CreateDate%2C%20MD.NextCreateDate%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3BVal(MD.CreateDate-MD.NextCreateDate)%20AS%20Dur%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EFROM%20(SELECT%20WONO%2C%20TradeID%2C%20StatusID%2C%20CreateDate%2C%20Nz((SELECT%20MIN(st2.CreateDate)%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20FROM%20WorkOrderFlow_Trade%20as%20st2%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20WHERE%26nbsp%3B%20st2.WONO%20%3D%20st.WONO%20AND%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bst2.ID%20%26gt%3B%20st.ID)%2CDATE())%20AS%20NextCreateDate%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%26nbsp%3BFROM%20WorkOrderFlow_Trade%20AS%20st%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EORDER%20BY%20WONO%2C%20ID)%26nbsp%3B%20AS%20MD%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20any%20idea%20i%26nbsp%3Bappreciate%20that%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20and%20Regards%3C%2FP%3E%3CP%3EFARZAD%20KHAJEH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1401504%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELEAD%20AND%20LAG%20Function%20in%20Ms%20Access%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi Dear All

In SQL Server we just have 2 function (LEAD & LAG) for calculating between a value in same column at different rows and i know about them.

In one Project i have to working with ms Access and that function i really need to.

I used below query and it works fine but it is very slow , for around 313000 record it will take 45 minutes or more , but in SQL Server with that functions (LEAD & LAG) it will take 1 sec. 

My Query :

SELECT MD.WONO, MD.TradeID, MD.StatusID, MD.CreateDate, MD.NextCreateDate,
 Val(MD.CreateDate-MD.NextCreateDate) AS Dur
FROM (SELECT WONO, TradeID, StatusID, CreateDate, Nz((SELECT MIN(st2.CreateDate) 
          FROM WorkOrderFlow_Trade as st2 
          WHERE  st2.WONO = st.WONO AND 
                         st2.ID > st.ID),DATE()) AS NextCreateDate
 FROM WorkOrderFlow_Trade AS st 
ORDER BY WONO, ID)  AS MD;

 

is any idea i appreciate that 

thanks and Regards

FARZAD KHAJEH

0 Replies