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?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,331
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
 

Gabbelgak

New Member
Joined
Dec 14, 2016
Messages
22
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,331
if the random data is an issue, maybe you can write an instance (of fixed values) to a temporary table
 

Watch MrExcel Video

Forum statistics

Threads
1,096,384
Messages
5,450,099
Members
405,586
Latest member
xkenxdizzle

This Week's Hot Topics

Top