Query not responding

[mor]

Hi everyone,

I have some SQL code that is giving me some bother. I am creating a qeury whereby I have four fields in a table (id_vl,id_product,date_vl,vl).

Basically each weekday I add a new value to VL (the value of a stock lets say) for each stock product that we are interested in. I want to find the number of days between each time I update the stock value. I have wrote the following SQL code that finds the number of days between each update of the stock values for each product up to a certain input date which is referenced [Forms]![F_BrokerageMandate_Main_Formulaire_Test]![Fees_End_Date]. I have tested this code on a small data sample (approx.20) that works perfectly with no problems whatsoever.

When I try to run the code on the real data sample (currently 1735 records) then the query is unbelievably slow and I haven't had the patience to see if it will eventually complete the query. Would anyone know why this is happening? Code is below

Code///


SELECT
tblVL.Id_VL,
tblVL.Id_Product,
tblVL.Date_VL,
tblVL.VL,

IIf((SELECT Date_VL FROM tblVL AS Alias_VL WHERE Date_VL = (SELECT Min(Date_VL) FROM tblVL AS Alias2_VL WHERE Alias2_VL.Date_VL > tblVL.Date_VL AND Alias2_VL.Id_Product = tblVL.Id_Product) AND Alias_VL.Id_Product = tblVL.Id_Product)>[Forms]![F_BrokerageMandate_Main_Formulaire_TEST]![Fees_End_Date],[Forms]![F_BrokerageMandate_Main_Formulaire_TEST]![Fees_End_Date],(SELECT Date_VL FROM tblVL AS Alias_VL WHERE Date_VL = (SELECT Min(Date_VL) FROM tblVL AS Alias2_VL WHERE Alias2_VL.Date_VL > tblVL.Date_VL AND Alias2_VL.Id_Product = tblVL.Id_Product) AND Alias_VL.Id_Product = tblVL.Id_Product)) AS Next_Date,

IIf(IsNull([Next_Date]),[Forms]![F_BrokerageMandate_Main_Formulaire_TEST]![Fees_End_Date],[Next_Date]) AS Forward_Date, DateDiff("d",[Date_VL],[Forward_Date]) AS Date_Diff

FROM tblVL

WHERE (((tblVL.Date_VL)<[Forms]![F_BrokerageMandate_Main_Formulaire_TEST]![Fees_End_Date]))

ORDER BY tblVL.Id_Product, tblVL.Date_VL, tblVL.VL;

\\\Code