Query performance using dlookup

[Guest]

I created a query to run against a table with 106 records. The query uses
dlookup to calculate a value based on the record before it.
The query takes a ridiculously long time to run. I have compressed and
repaired. Is there anything else I can do to speed up the query? The sql is
below

Thanks
DMG

SELECT tblNewCusip.ID, tblNewCusip.Cusip, tblNewCusip.Memo, tblNewCusip.CPS,
IIf([NewCusip]=0,0,DLookUp("[Lot]","[qryTake]","[ID]=" &
[ID]-1)+DLookUp("[Total]","[qryTake]","[ID]=" & [ID]-1)) AS Total,
IIf([Total]<[tblInkindTrades_Shares],IIf([qryLowCost_Shares]>([tblInkindTrades_Shares]-[Total]),([tblInkindTrades_Shares]-[Total]),[qryLowCost_Shares]),0)
AS Lot
FROM tblNewCusip;

 

[Guest]

It's more than the DLookUp slowing things down. You also have a couple other
queries running inside it plus IIf statements. It's actually pretty good that
it is running at all.

I have two suggestions:
1. Normalize your data so that you don't have to jump through all these
hoops to get an answer.
or
2. Put it in Excel as it, pardon the pun, excels at these kinds of
calculations.

 

[Guest]

In the query qryTake add another Id field + 1

Select TableName.*, [ID]+1 As NewId From TableName

Then in the new SQL join tblNewCusip Table with qryTake using
tblNewCusip.[ID] = qryTake.[NewId]

So, you wont need to use dlookup, just display the return field using the
link between the two tables

 

[Guest]

Ofer
Thanks for the suggestions. I am fairly new to this so excuse me if I ask a
dumb question. I am in design view on the qryTake. I added NewID = ID +1.
Your next step is to join my table and my query. Is this in a new query?

In the query qryTake add another Id field + 1

Select TableName.*, [ID]+1 As NewId From TableName

Then in the new SQL join tblNewCusip Table with qryTake using
tblNewCusip.[ID] = qryTake.[NewId]

So, you wont need to use dlookup, just display the return field using the
link between the two tables

--
\\// Live Long and Prosper \\//
BS"D

I created a query to run against a table with 106 records. The query uses
dlookup to calculate a value based on the record before it.
The query takes a ridiculously long time to run. I have compressed and
repaired. Is there anything else I can do to speed up the query? The sql is
below

Thanks
DMG

SELECT tblNewCusip.ID, tblNewCusip.Cusip, tblNewCusip.Memo, tblNewCusip.CPS,
IIf([NewCusip]=0,0,DLookUp("[Lot]","[qryTake]","[ID]=" &
[ID]-1)+DLookUp("[Total]","[qryTake]","[ID]=" & [ID]-1)) AS Total,
IIf([Total]<[tblInkindTrades_Shares],IIf([qryLowCost_Shares]>([tblInkindTrades_Shares]-[Total]),([tblInkindTrades_Shares]-[Total]),[qryLowCost_Shares]),0)
AS Lot
FROM tblNewCusip;