Hi,

In oracle I can limit the number of records returned with a subselect like: select * from(select rownum as id, field1, filed2, field3 from tbl1) where id between 20 and 30.

How can I do this in access 2003? I know I can use this: select top 20 field1 from tbl1, but this returns the first 20 records.

Thanks

You can use nested queries in Access just as you have described for Oracle. I generally use SQL view rather than the design grid view to construct nested queries.

Edited by: jzwp11 on Thu Jun 26 16:51:51 EDT 2008.

Access doesn't seem to support rownum? How can I identify the row number to use in a subselect.

Thanks

I don't understand what you are trying to achieve.... do you want to return a number of records where the ID is between 20 and 30?

Yes that is what I'm trying to achieve, return records 20 to 30. Sorry if I have made this confusing.

Have you tried just using criteria



You could of course also chain in a top statement

Sorry ID is an alias for rownum in the oracle query. I can't use criteria because the field ID does not exist (The table I'm querying is a linked table (odbc)).

I just wondered if access had similar functionality to oracle's rownum?

Thanks for your help

Not built in but check out from the code archive : http://www.utteraccess.com/forums/showflat...p;Number=367553

Thank-you Danny for the link that's just what I was looking for.

you're welcome