>Any suggestions or clues how I might optimize this SQL, it is running via ADO?
>
>lcPreviousId = ThisRecord.Id
>
>* Previous record:
>SELECT * FROM Master WHERE (Id < lcPreviousId) UNION ;
> SELECT * FROM Archive WHERE (Id < lcPreviousId) ;
> ORDER BY Id DESC
>
>* Next record:
>SELECT * FROM Master WHERE (Id > lcPreviousId) UNION ;
> SELECT * FROM Archive WHERE (Id > lcPreviousId) ;
> ORDER BY Id ASC
>
>Thsnks,
> Nick
Using derived tables you can combine both selects into one
select * from (select * From Master where ID < lcPreviousID UNION ALL
select * from Archive where ID < lcPreviousID order by ID Desc)
UNION ALL
select * from (select * From Master where ID > lcPreviousID UNION ALL
select * from Archive where ID > lcPreviousID order by ID ASC)
But what result do you want to achieve with this query - get all records except for current record?
If it's not broken, fix it until it is.
My Blog