Show 2nd, 3rd, etc. Result in a Query only

Gabbelgak

New Member
Joined
Dec 14, 2016
Messages
22
Hello, I'm trying to find out how to display only the Nth result in a query. I have a query currently that shows all Categories of question types possible, assigns a random integer and limits the results to the top 5. On my form I want to display each of those results in a separate box.

The query I want to base everything off looks like this currently:
SELECT TOP 5 tblCategories.Category, tblCategories.AutoNumber
FROM tblCategories
ORDER BY Rnd([AutoNumber]);

Getting the top result in the first box on my form I can just do:
SELECT TOP 1 qryCategorySelector.Category
FROM qryCategorySelector
ORDER BY qryCategorySelector.AutoNumber;

and getting the bottom result I just order by DESC and its working.

How do I get the correct result for my 2nd, 3rd and 4th box?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
or, second top

Code:
SELECT TOP 1 A.CategoryFROM (
SELECT TOP 4 Q.category, Q.autonumber
FROM YourQuery Q
ORDER BY Q.autonumber DESC) A
ORDER BY A.autonumber ASC

and third top,
Code:
SELECT TOP 1 A.Category
FROM (
SELECT TOP 3 Q.category, Q.autonumber
FROM YourQuery Q
ORDER BY Q.autonumber DESC) A
ORDER BY A.autonumber ASC

and fourth top
Code:
SELECT TOP 1 A.Category
FROM (
SELECT TOP 2 Q.category, Q.autonumber
FROM YourQuery Q
ORDER BY Q.autonumber DESC) A
ORDER BY A.autonumber ASC
 
Upvote 0
I've tried to get both of these to work with no luck. I believe the hickup on yours Fazza is because of the random element I have on the query. Each of those queries will create a new Rnd seed query which can cause me to get duplicate results.

I'm trying to create a very rudimentary version of Jeopardy to help people at the office with studying and am just trying to have it pick and show 5 random categories for me on a single form at once if anyone has an alternative idea to try.
 
Upvote 0
if the random data is an issue, maybe you can write an instance (of fixed values) to a temporary table
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top