Qry By Record Number 1

[benmillus]

Need to query a table without row numbers using row numbers. Like in pl-sql we can write the qry "select * from siebel.s_opty where rownum < 10;" Is there anything similiar to "rownum" in MS Access?

 

[lameid]

I'm not familiar with pl-sql but it sounds like you want a Top Values clause... in the QBE Right click the gray space where the tables are at top. Next find top values and specify a number like 10. Then close the properties window and check datasheet for behavior and SQL view for syntax. It can take both a number and a percent as a value.

 

[benmillus]

This is good. But I also need to select a range of records which are not always at the top. For example, select * from my.table where record_num between 2 and 5.

 

[lameid]

The only other thing that comes to mind is using an Autonumber to number the records but would not necessarily be the order of your result set.

Perhaps if I knew why you want to do this I might be able to come up with a better idea.

 

[benmillus]

Autonumber sounds like the right path. But not sure how to implement. We have a table that 'users' are querying via a 'user' proof tool. They request the ability to query in 4 ways by unique value 1, unique group 1, range or all. I have all but the range covered. For the range, the users know the layout of the table. So for example they know exactly what row 10 is even though it is not labeled such.

 

[lameid]

Sounds like mainframe thinking <cringe>.
Anyway, add a field to your table and make it's datatype autoneumber. It should default to a size of long integer and new values of Increment. Everytime a record is added, the value increments.

Caveats to your solution: Deleted records put a gap in the sequence. Editing a new record and cancelling uses up an autonumber.

If you showed the autonumber field and let them shoose between the field values for criteria, you might make them happy enough.

If they get really anal about not liking it, you can at least use Autonumber to sort the fields. Then for records 2 to 5 you can get the Top Values of 5. Then get the top (5 - 2 - 1) values of that query with the values sorted descending. It will take longer but it will work.