show records 4 - 8 3

[webware]

I am running the following query and want to know how to display records 4 thru 8

SELECT house.P1, Count(*) AS EXPR
FROM house
GROUP BY house.P1
HAVING (((Count(*)+1)<>False))
ORDER BY Count(*)DESC;

 

[ToeShot]

SELECT house.P1, Count(*) AS EXPR
FROM house
WHERE house.P1 BETWEEN '4' AND '8'
GROUP BY house.P1
HAVING (((Count(*)+1)<>False))
ORDER BY Count(*)DESC; Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[Steve101]

ToeShots method will work assuming that your House.P1 field has guarenteed field values for P1 of '1', '2', '3', '4', '5' ... and so on. It also assumes that the data is presented in the same ascending order. I'm not convinced that these assumptions are what webware intended.

I dont think that you can do what you want in &quot;straight&quot; SQL. Rather let me direct you to the GetRows method of the ADO recordset object. This method allows you to do &quot;paging&quot; within a Recordset, whereby you can specify the relative starting point within the recordset (eg. the 4th record), and number of records to retrieve, based on the recordset definition

Hope this helps,
Cheers,
Steve

 

[raskew]

Hopefully, this will be transferable to your data.
It incorporates two queries

Using Northwind's Products table, the intent is
to isolate the 4th - 8th products in an alpha sort
listing.

Query 20:

SELECT TOP 8 Products.ProductName
FROM Products
ORDER BY Products.ProductName;

Query 21:

SELECT top 5 Query20.ProductName
FROM Query20
ORDER BY Query20.ProductName DESC;

This will return records 4-8 from Query20,
but sorted in reverse order. If, in preview
mode, you click on Sort Ascending from
the toolbar, then save the query, it will from
that point on display the records in the
desired sequence. (It's apparently saving
the layout of the query, without modifying
the query SQL.)