Next/Previous Record in database for query

Hi,

I have an access database of 250 records. I also have an admin section where the staff is viewing each record of the database. When viewing the data of the individual record, they would like to move to the next record or the previous record.  The query does need to be limited by status.

<cfquery name="qry_previous" datasource="#request.dsn#" dbtype="ODBC">
SELECT grantid
FROM grant
WHERE grantid = #url.grantid# AND status IS NULL
ORDER BY grantID DESC  
</cfquery>


Any suggestions? I only need to get the very next record and the very previous record.

Thanks
MameaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
try something like this:


SELECT grantid,
(select top 1 p.grantid from grant p where p.grantid < grant.grantid  AND status IS NULL order by p.grantid desc) as previousid,
(select top 1 p.grantid from grant p where p.grantid > grant.grantid  AND status IS NULL order by p.grantid) as nextid
FROM grant
WHERE grantid = #url.grantid# AND status IS NULL
ORDER BY grantID DESC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZvonkoSystems architectCommented:
You need three queries. Not all three will give results every time e.g. when first grantid is searched.
Check this:
<cfquery name="qry_previous" datasource="#request.dsn#" dbtype="ODBC">
SELECT TOP 1 grantid FROM grant
WHERE grantid < #url.grantid# AND status IS NULL
</cfquery>
<cfquery name="qry_theone" datasource="#request.dsn#" dbtype="ODBC">
SELECT grantid FROM grant
WHERE grantid = #url.grantid# AND status IS NULL
</cfquery>
<cfquery name="qry_next" datasource="#request.dsn#" dbtype="ODBC">
SELECT TOP 1 grantid FROM grant
WHERE grantid > #url.grantid# AND status IS NULL
</cfquery>

Open in new window

0
ZvonkoSystems architectCommented:
Another way is to use min() and max() functions:
<cfquery name="qry_previous" datasource="#request.dsn#" dbtype="ODBC">
SELECT max(grantid) FROM grant
WHERE grantid < #url.grantid# AND status IS NULL
</cfquery>
<cfquery name="qry_theone" datasource="#request.dsn#" dbtype="ODBC">
SELECT grantid FROM grant
WHERE grantid = #url.grantid# AND status IS NULL
</cfquery>
<cfquery name="qry_next" datasource="#request.dsn#" dbtype="ODBC">
SELECT min(grantid) FROM grant
WHERE grantid > #url.grantid# AND status IS NULL
</cfquery>

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

eszaqCommented:
Try to use built-in cfquery variales when generating your output:
     query_name.CurrentRow
     query_name.RecordCount

Sorry, do not have access to CF server at the minute, so did not test. Anyway, something like this:


<cfparam url.recordnum = "1">
 
<cfoutput 
   query = "query_name"
   startRow = "#url.recordnum#"
   maxRows = "1">
   
   <cfif query_name.CurrentRow GT 1>
   <a href="?recordnum=#evaluate(query_name.CurrentRow-1)#">previous</a>
   </cfif>
   
   #query_name.grantid#
   
   <cfif query_name.CurrentRow LT query_name.RecordCount>
   <a href="?recordnum=#evaluate(query_name.CurrentRow-1)#">next</a>
   </cfif>
   
</cfoutput>

Open in new window

0
eszaqCommented:
oops, of course for next record it would be
#evaluate(query_name.CurrentRow+1)#
0
MameaAuthor Commented:
Thank you! Your code worked perfectly! I didn't have to edit anything. Thank you for your quick response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.