Select TOP n records (1 Viewer)

kmwhitney

New member
Local time
Today, 08:51
Joined
Aug 5, 2020
Messages
3
I have a query that I want to select the top n records from the results of another query. Each time the user runs the query they could want any number of results so I want the value to be a variable that they enter in. I do this in the other query as well where the user enters variable start and end dates but I cannot get the variable to take for this query using the TOP function. Code for each query below.

First Query:
Code:
SELECT Base_Prefixes.SUC_prefix
FROM Base_Prefixes LEFT JOIN SUC_Prefix_Tracker ON Base_Prefixes.SUC_prefix =  SUC_Prefix_Tracker.SUC_Prefix
WHERE Base_Prefixes.SUC_Prefix not in

(Select SUC_Prefix_Tracker.SUC_Prefix
from SUC_Prefix_Tracker
WHERE startdate <= SUC_Prefix_Tracker.End_Date
and enddate>= SUC_Prefix_Tracker.Start_Date)


or SUC_Prefix_Tracker.Start_Date is null
ORDER BY Base_Prefixes.SUC_Prefix;

The result of this is a list of 2 letter prefixes

Second Query ( Error Message: the SELECT statement includes a reserved word....)
Code:
SELECT  TOP " & prefixqty & "  [Available Prefixes].SUC_prefix
FROM [Available Prefixes];
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:51
Joined
Oct 29, 2018
Messages
21,357
Hi. How exactly are you modifying the query's SQL statement. For the TOP predicate, it can only be done through a QueryDef object.
 

Micron

AWF VIP
Local time
Today, 08:51
Joined
Oct 20, 2018
Messages
3,476
You could simply enter the value again seeing as how you don't seem to mind the prompts. Or you could use a control (textbox or spinner?) on the form and refer to its value from each query = no prompts.
EDIT - upon further review, I gather that the TOP predicate value cannot be parameterized so you'd have to do this in code; either by modifying a querydef or by executing a sql statement written in code.
 
Last edited:

kmwhitney

New member
Local time
Today, 08:51
Joined
Aug 5, 2020
Messages
3
Hi. How exactly are you modifying the query's SQL statement. For the TOP predicate, it can only be done through a QueryDef object.


Like in the first query where I use "startdate" and "enddate" to prompt the user to enter information into a message box, I was hoping I could do that for the second query as well where when the query was run a message box would appear and the user enters the number of records they want. however, I am not as experienced in Access as I thought because I do not know how to set up a QueryDef object.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:51
Joined
Oct 29, 2018
Messages
21,357
Like in the first query where I use "startdate" and "enddate" to prompt the user to enter information into a message box, I was hoping I could do that for the second query as well where when the query was run a message box would appear and the user enters the number of records they want. however, I am not as experienced in Access as I thought because I do not know how to set up a QueryDef object.
Unfortunately, you can prompt the user for columns and criteria, but not for the TOP predicate. Let me see if I can find an example of how to use a QueryDef object. It would probably mean using a Form for the user's input, instead of just prompting them for it.

Edit: Okay, see if you can learn something from this.
 

kmwhitney

New member
Local time
Today, 08:51
Joined
Aug 5, 2020
Messages
3
Unfortunately, you can prompt the user for columns and criteria, but not for the TOP predicate. Let me see if I can find an example of how to use a QueryDef object. It would probably mean using a Form for the user's input, instead of just prompting them for it.


Thanks so much!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:51
Joined
Oct 29, 2018
Messages
21,357
You're welcome. Let us know how it goes or if you get stuck. Cheers!
 

Isaac

Lifelong Learner
Local time
Today, 05:51
Joined
Mar 14, 2017
Messages
8,738
Whenever I mess with a saved QueryDef's .Sql property, which is a favorite method of mine and simple as heck ... I often use placeholder text.
Might go something like this:
- save one query, QueryName_Original. in this query you fix it up exactly how you want it, then view the SQL and change the literal criteria (or in your case, TOP clause) with something like "placeholder". If your SQL says select top 10...change it to say select top placeholder...and save it
- save another query with pretty much any SQL you want, in the world, as long as it is valid and can be saved.
- code something like:
Code:
dim strNumberofRecords as string
strNumberOfRecords=inputbox("enter number of top value")
currentdb.querydefs("secondqueryname").sql=replace(currentdb.querydefs(" QueryName_Original ").sql,"placeholder",strNumberOfRecords
..do whatever with the query
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:51
Joined
Feb 19, 2013
Messages
16,553
think your first query would be more efficient if written like this

Code:
SELECT Base_Prefixes.SUC_prefix

FROM Base_Prefixes LEFT JOIN

    (Select SUC_Prefix_Tracker.SUC_Prefix
         FROM SUC_Prefix_Tracker
         WHERE startdate <= SUC_Prefix_Tracker.End_Date
              AND enddate>= SUC_Prefix_Tracker.Start_Date) AS Exclude 

     ON Base_Prefixes.SUC_prefix =  Exclude.SUC_Prefix

WHERE Exclude.SUC_Prefix is Null

ORDER BY Base_Prefixes.SUC_Prefix

probably not noticeable on smaller number of records, but will be on larger numbers
 

Users who are viewing this thread

Top Bottom