Query Help Again

[carl]

I am trying the following SQL (Thank You Ofer).

SELECT DurationTable.NewID, DurationTable.ExternalSymbol,
IIf(nz(DLookUp("[ExternalSymbol]","[DurationTable]","[NewID] = " &
Nz(DMax("[NewID]","[DurationTable]","[NewID] < " &
[NewID]),0)),0)=[ExternalSymbol],"TRUE","FALSE") AS Condition,
DurationTable.NewTime, DurationTable.Duration1, DurationTable.Date,
DurationTable.MessageType, DurationTable.FirmId, DurationTable.ISIN,
DurationTable.SubscriberId, DurationTable.TraderId,
DurationTable.InternalSubcriberRef
FROM DurationTable;

The SQL generates the table below. However, it seems to "recalc" everytime I
switch to and back to the displayed result.



NewID ExternalSymbol Condition Duration1
1 UMUD06C30.00 FALSE
2 QAAE06C65.00 FALSE 0:00:21
3 QAAE06C65.00 TRUE 0:00:04
4 DLQD06C30.00 FALSE 0:00:09
5 GG E06C32.50 FALSE 0:00:03

I tried to use the "make-table" query but could not get it to complete (took
too long and Access became unresponsive).

Is there a way for this query to generate the result but not be linked to
the original table - I only need the result and do not care if a value in the
original table changes.

I hope I explained my problem.

Thank you in advance.

 

[Jeff Boyce]

Carl

"Select" queries are dynamic. They run each time you run them. If you only
need a static snapshot of data, use a make-table query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Don't know if this will work or not, but you might try this and see what it
does for you.

SELECT DurationTable.NewID
, DurationTable.ExternalSymbol
, IIF((SELECT ExternalSymbol
From DurationTable as D2
WHERE D2.NEWID =
(SELECT Max(NewID)
From DurationTable as D1
Where D1.NewID < DurationTable.NewID)) <> ExternalSymbol,
"False","True") as Condition
, DurationTable.NewTime
, DurationTable.Duration1
, DurationTable.Date
, DurationTable.MessageType
, DurationTable.FirmId
, DurationTable.ISIN
, DurationTable.SubscriberId
, DurationTable.TraderId,
DurationTable.InternalSubcriberRef
FROM DurationTable;


If that is slow, you can try turning it into a make table query and see if
it works.