Go to previous record in sql query

[usr123]

I am trying to get the previous record from a table.
Scenario is:
I have a previous button on the form. User browse through the records one by one to go back/forward.

For first time to pull data from sql,i check that if form is empty,if it is then i pull the last record form the table based on Max(id) which is autonumber.

Now, because people would be deleting record as well,there is autonumber not in any consecutive order.
Once i have got the lasr record, and when user click on previous button again, how can i pull the second last record and so on.

ANy suggestions would be a great help!!
Thanks!

[usr123]

Once i have got the max(id),then to get the next last ID from the table, i need somethign like:

select * from table where id = (select max(id) - 'look for first mandatory filed not equal to zero' and get that id number)

how can i do that in sql?

[usr123]

ODBC call failed error '3146'...

[usr123]

How can i close this thread? i have opened another one for teh same problem in detail...

[ck9663]

There are a lot of option for you. For one, you don't have to actually delete a record. You just need a flag to say the record is active or not. It should keep your record history.

If that's not an option, try this:
1. Display the current ID on the application.
2. User clicks NEXT
3. select top 1* from yourtable where CurrentID > IDOnYourTable order by ID desc.

This would be slow though, specially if your table is big.

Good Luck!!!

~~ CK

[usr123]

Thank you very much for ur reply and suggestion . I did it by attaching flag to the deleted record.
Query sugesstion is very good coz i didnt know that it could be done in sql that way.
Thanks