Jun 26 2008, 03:40 PM
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.
Jun 26 2008, 03:50 PM
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.
Jun 26 2008, 04:00 PM
Access doesn't seem to support rownum? How can I identify the row number to use in a subselect.
Jun 26 2008, 04:24 PM
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?
Jun 26 2008, 04:34 PM
Yes that is what I'm trying to achieve, return records 20 to 30. Sorry if I have made this confusing.
Jun 26 2008, 05:14 PM
Have you tried just using criteria
SELECT Field1 FROM YourTableName WHERE ID Between 20 and 30
You could of course also chain in a top statement
SELECT TOP 3 Field1 FROM YourTableName WHERE ID Between 20 and 30
Jun 26 2008, 05:24 PM
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
Jun 26 2008, 05:45 PM
Jun 27 2008, 10:55 AM
Thank-you Danny for the link that's just what I was looking for.
Jun 29 2008, 02:28 AM
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here