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:
The result of this is a list of 2 letter prefixes
Second Query ( Error Message: the SELECT statement includes a reserved word....)
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];