Select TOP n records

[kmwhitney]

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:

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....)

SELECT  TOP " & prefixqty & "  [Available Prefixes].SUC_prefix
FROM [Available Prefixes];

 

[theDBguy]

Hi. How exactly are you modifying the query's SQL statement. For the TOP predicate, it can only be done through a QueryDef object.

 

[Deleted member 147267]

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.

 

[kmwhitney]

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]

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.

Create a Query using VBA | DEVelopers HUT

The code below allows you to supply a query name and an SQL statement, and it will create the query for you.

www.devhut.net

 

[kmwhitney]

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]

You're welcome. Let us know how it goes or if you get stuck. Cheers!

 

[Isaac]

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:

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]

think your first query would be more efficient if written like this

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