I have table called Jobs. It has approx 200,000 records. The Primary Key field is JobNo. I have ascending and descending indexes on this field. For any given JobNo I need to be able to quickly find the next or previous JobNo's. So to find the next JobNo i use the following query:
SELECT TOP 1 JobNo From Jobs WHERE JobNo > 193574 ORDER BY JobNo
This executes instantly and gives the correct result. To find the previous record, I am using this:
SELECT TOP 1 JobNo From Jobs WHERE ((JobNo<193574)) ORDER BY JobNo Desc
This takes 10 seconds to open even though the JobNo field has a descending Index on it. I am thinking that Access is finding the PrimaryKey ascending index and doesn't look any further. So it doesn't find the descending Index. Can anyone suggest a way I can speed this up at all?