Query limits

[cynicon]

Hi again,

I have an access query like this:

"SELECT Sheet1.* FROM Sheet1;"

The result is about 1,000 or 1,500 records.

How can I limit the result, showing only from record number 50 to record number 100.

Is it possible?

BTW, I have no ID field (just in case)

[Stewart Ross]

Hi Cynicon, and Welcome to Bytes!

There is no single SQL operator which can do what you want. The nearest is SELECT TOP:

SELECT TOP 100 yourfield1, yourfield2 etc FROM yourtable ... etc

This will return the top 100 records in whatever table or query you wish to reduce the volume on, but you cannot exclude the first 50 that way. It may be possible to do it by using right-joining between a SELECT TOP 100 query and a SELECT TOP 50 query, returning only the rows where keys don't match, but I reckon this would be a very slow approach (as you would be running the complete dataset twice simply to get rid of the first N rows from the result set).

There may be other ways to do this using subqueries, but without seeing your data it is difficult to give you more guidance. If you could post some example data we could review it and see if there is a way to help you that does not involve considerable run-time processing.

You mention that you don't have an ID field - not sure why not, as it makes joining the results much more difficult if there is no unique ID available.

-Stewart

[beacon]

Will it mess up your data too much to add an ID field?

I would think that you could loop through the recordset until you get to record number 50 and stop when you get to record number 100, but I'd have to play with it to get it to filter according to only those values.

Like Stewart mentioned, without knowing what kind of data is stored in the tables, it's almost impossible to give you a solution that will work in all instances.

[NeoPa]

I don't think you'd expect a facility such as this in something like SQL. However, Stewart has shown how to do it. Simply join two subqueries (See Subqueries in SQL for help with that) with the TOP predicates set to 50 & 100 and include only those that don't match on a unique ID. If you don't have one of those (or any way of unique identification) then you need to think about adding one. There's a good reason for them. We don't just create them for fun. They're fundamental to most database work.

[cynicon]

Sorry about the delay in my reply all of you have been so kind while giving answers, Ill check all of your answers and let you know as asap the results, thank you

[NeoPa]

That's perfectly reasonable. As it's a web-based forum, we don't expect immediate responses :)