ROWNUM, How to get it?

[chumporn]

Hi,

I'd like to specific "row number" to show in query such as we have full result record of query is 100. I knew how to use fuction SELECT TOP for first n row from top. But I need to select among of them such as row 21st thru 40th from all 100 records list.

In Oracle, they have "rownum" to specific row number which available to scope range of result record to show. Hence, how to get it in MS Access?

Please help me.

Regards,
Chumporn P.

 

[boblarson]

In Access data is not stored in any order in the tables, so what you THINK is ordered, just because it may have it in autonumber sequence at any point in time, is not guaranteed. In order to have meaning to an order you have to impose it yourself. So, either by a date/time stamp or an ordered number (not autonumber as it doesn't have to increment up one each time - in fact if you use replication, it will jump around like -23954233 or 3912993. )

Then, and only then will you be able to reliably search for a particular record and get the same data returned each time.