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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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,368
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,102,164
Messages
5,485,125
Members
407,481
Latest member
junniec

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top