Guestbook Multiple Pages (1 Viewer)

[DeltaVega]

Hello everyone!

I'm trying to make a simple guestbook database with MS Access 2000 to be used by an ASP script.

What I need is a way to return only 5 records each time the query is called and a variable that tells the query where to start. So if the variable given to the query is 0 it will return records 1,2,3,4,5. If the variable is 5 it will give records 6,7,8,9,10. ..etc.

I can do this on the application level using ASP. But the problem is that the recordset it recieves contains all of the DB's records. So it would be faster to do on the database level, before the recordset reaches the script.

Right now, my query is nothing more than:

SELECT *
FROM Guestbook
ORDER BY [guestbook_date] DESC;

Hope you can help!
Bye!

 

[Fuga]

The idea is this, allthough it may not be the exact phrasing:

SELECT top 5 *
FROM Guestbook
WHERE [guestbook_date] > [yourvariable]
ORDER BY [guestbook_date] ASC;

Are you sure you want the 5 records following the current?

Fuga.

 

[DeltaVega]

Well, as I understand it, TOP returns the top 5 percent of total records.

What I need is an absolute number of records. Plus The WERE condition you supplied doesn't work.

To make matters clearer, the application is just like this forum, where you get a certain number of entries per page, and at the bottom you have Next, Back, and page numbers.

But thanks for your help anyway Fuga!

P.S. Isn't there an SQL command to scroll past records depending on a certain condition? I also don't *know* how to limit the records to an absolute number!

 

[Fuga]

Ok, I´m not sure how your db works, but I still think you should use something like this:

Dim yourvariable as string
Dim rs as recordset

set rs = currentdb.openrecordset("select top 5 * from guestbook where [guestbookfield] > " & yourvariable & " order by [guestbookfield] asc")

Provided the guestbookfield contains a value showing the record to be later than other records.

But then again I´m not sure how you want it to work. How do you assign the value to yourvariable etc.

Maybe it will work with a parameter query?

Fuga.

 

[Fornatian]

JFI- Top doesn't just return a percentage.

TOP n [PERCENT]

Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause.

Suppose you want the names of the top 25 students from the class of 1994:SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you don't include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.The TOP predicate doesn't choose between equal values.

In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause.

Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer.TOP doesn't affect whether or not the query is updatable.

 

[Jon K]

You can use a subquery to rank the records and use Between... And... to retrieve 5 records each time a variable 0,1,2 etc is entered.


Add a RecordID field (an AutoNumber field) in the Guestbook table and run this query:-

SELECT *
FROM GuestBook AS a
WHERE (select count(*) from GuestBook where RecordID >=a.RecordID) between [Variable]*5+1 and [Variable]*5+5;


The subquery (the select statement in brackets) ranks the records in descending order of RecordID, so when 0 is enter for Variable, the most recent 5 records will be returned. When 1 is entered, the next 5 records will be returned.

To rank the records in ascending order of RecordID, just use <=

--------------------------

The attached demo DB adapts the query in a form. (Open the form and click on the "Return next 5 records" button repeatedly to retrieve the records.) The code is all in the Click Event of the command button.

The code was written in Access 97 so DAO was used. If you write the code in Access 2000, you must make a reference to the Microsoft DAO 3.6 Object Library (when the code window is open, choose menu Tools, References...)

Hope you can adapt it to suit your needs.

 

[DeltaVega]

I've thought about using an autonumber field but I quickly realized that if you delete any of the records, the solution above breaks. There must be no holes in the numbering sequence and that's not very realistic.

In this case, TOP seems to be my only option. But I still don't see how I can get the next five records and the five after that.

In abstract we can tell the database to count five records and return them. But can we tell it to count five, then count another five x number of times and return the last five counted?

I can't believe how difficult this seems seeing the sheer number of actual application of this concept on the net. I mean, we're using one on this forum, right?

 

[Jon K]

The AutoNumber here is solely for the ranking purpose. It is used as a convenient tool. The query pulls the records not from the AutoNumbers but in fact using CALCULATED ranks.

Deleting records would not affect the calculated ranks.

In fact, the query works so long as there is a field that can
1) uniquely identity the records; and
2) be compared using >= or <=

For instance, if the Guestbook_Date field contained also the time, it could have been a perfect candidate for calculating the ranks.

If you delete some records in the demo DB, you will find that the statement "There must be no holes in the numbering sequence" doesn't hold.

 

[DeltaVega]

You're right Jon. I see now how your query works.

This is the perfect solution to my problem.

Thanks a lot for your help, I could NOT have done this by my own!