Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Returning Next and Previous Record ID

Hi All,

Could anyone share their wisdom with me...

I have the attached SQL in MS Access and what I would like to do is along with the columns specified I would also like to return the next quizID and previous quizID.

I can't presume that the quizID run consecutively as the query is sorted by the sortOrder column.

Any help would be appreciated.

Many thanks,

Rit
SELECT 
	tblQuiz.quizID, 
	tblQuiz.question, 
	tblAnswers.AnswerText, 
	tblAnswers.userID
	
FROM tblQuiz LEFT JOIN tblAnswers ON tblQuiz.quizID = tblAnswers.quizID

WHERE (tblAnswers.userID)=[paramMyID])

ORDER BY tblQuiz.sortOrder

Open in new window

0
rito1
Asked:
rito1
3 Solutions
 
als315Commented:
You can use grouping:
Max on quizID where quizID < Your_quizID
and
Min on quizID where quizID > Your_quizID
0
 
OP_ZaharinCommented:
if quizID is a confirmed running number (no missing number) you can use MAX to find the latest quizID number then + 1 to find the next number and - 1 to find the previous one:

SELECT 
	tblQuiz.quizID, 
        MAX(tblQuiz.quizID) + 1,
        MAX(tblQuiz.quizID - 1,
	tblQuiz.question, 
	tblAnswers.AnswerText, 
	tblAnswers.userID
FROM tblQuiz LEFT JOIN tblAnswers ON tblQuiz.quizID = tblAnswers.quizID
WHERE (tblAnswers.userID)=[paramMyID])
ORDER BY tblQuiz.sortOrder

Open in new window

0
 
ralmadaCommented:
Try the attached.

You might want to double check the filtering conditions since I'm just guessing here so basically try it with and without

... question = A.Question ....
SELECT 
	A.quizID, 
	(select top 1 quizID from tblQuiz where question = A.Question and quizID > A.QuizID order by quizID) as NextQuizID,
	(select top 1 quizID from tblQuiz where question = A.Question and quizID < A.QuizID order by quizID DESC) as PrevQuizID,
	A.question, 
	B.AnswerText, 
	B.userID

FROM tblQuiz as A
LEFT JOIN tblAnswers as B ON A.quizID = B.quizID
WHERE (B.userID)=[paramMyID])
ORDER BY A.sortOrder

Open in new window

0
 
rito1Author Commented:
Thanks all.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now