Parameterizing a SELECT TOP x statement

  • Thread starter Thread starter sfleck
  • Start date Start date
S

sfleck

In an Access DB ( Access 2K3) I have the following querie that work perfectly
except I would like paramerterize the # so if i want the top 25 then I imput
25 and if i require 10 i enter 10

SELECT TOP 25 MailFairTable.AccountNumber, MailFairTable.School,
MailFairTable.WhsePullDate, MailFairTable.WhseSheetPulled
FROM MailFairTable
WHERE (((MailFairTable.WhseSheetPulled)=0));
 
see "TOP in query user selects amount to return" a couple of lines above.


Vanderghast, Access MVP
 
Select Top N records where N is variable ( a parameter)

You can't do this with a parameter query as
SELECT TOP [How Many] FieldA, ...

You can do that with one of the following methods.


One method that can be used is to use a ranking query to assign a number to
the records and then use the ranking to return N records.

'Probably not updatable
SELECT MyTable.*
FROM MyTable INNER JOIN
(
SELECT A.MyField, Count(B.MyField) as TheCount
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
) as Ranking
ON MyTable.MyField = Ranking.MyField
WHERE Ranking.TheCount < [Top How Many Number]

'Probably updatable
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) < [Top How Many Number])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
I have a question about Myfield

What is the count counting?

I am looking to return the next x number of records in line that are due to
be worked
for example 1 day staffing allows for 20 but the next day it is only 15.

I am looking to enter the value that will then return that many records

If I the example below it return all the records avail.

John Spencer said:
'Probably updatable
Click to expand...
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) < [Top How Many Number])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
In an Access DB ( Access 2K3) I have the following querie that work perfectly
except I would like paramerterize the # so if i want the top 25 then I imput
25 and if i require 10 i enter 10

SELECT TOP 25 MailFairTable.AccountNumber, MailFairTable.School,
MailFairTable.WhsePullDate, MailFairTable.WhseSheetPulled
FROM MailFairTable
WHERE (((MailFairTable.WhseSheetPulled)=0));
Click to expand...
Click to expand...
 
This query is going to return 25 records (or more) based on some
unspecified order - You did not specify an order which you should
normally do with a Top query. It is probably using your primary key
field to determine the top 25 records, but that is a guess.

SELECT TOP 25 MailFairTable.AccountNumber, MailFairTable.School,
MailFairTable.WhsePullDate, MailFairTable.WhseSheetPulled
FROM MailFairTable
WHERE (((MailFairTable.WhseSheetPulled)=0));

I can guess that the order is determined by AccountNumber

SELECT MailFairTable.AccountNumber, MailFairTable.School,
MailFairTable.WhsePullDate, MailFairTable.WhseSheetPulled
FROM MailFairTable
WHERE (((MailFairTable.WhseSheetPulled)=0))
AND AccountNumber In
(SELECT A.AccountNumber
FROM MailFairTable as A LEFT JOIN MailFairTable As B
ON A.AccountNumber < B.AccountNumber
WHERE A.WhseSheetPulled = 0
GROUP BY A.AccountNumber
HAVING Count(B.AccountNumber) < [Top how many?])

That should return the Top 20 Account Numbers if you respond 20 to the
prompt.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a question about Myfield

What is the count counting?

I am looking to return the next x number of records in line that are due to
be worked
for example 1 day staffing allows for 20 but the next day it is only 15.

I am looking to enter the value that will then return that many records

If I the example below it return all the records avail.

John Spencer said:
'Probably updatable
Click to expand...
SELECT MyTable.*
FROM MyTable
WHERE MyField in
(SELECT A.MyField
FROM MyTable as A LEFT JOIN MyTable As B
ON A.MyField < B.MyField
GROUP BY A.MyField
HAVING Count(B.MyField) < [Top How Many Number])

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
In an Access DB ( Access 2K3) I have the following querie that work perfectly
except I would like paramerterize the # so if i want the top 25 then I imput
25 and if i require 10 i enter 10

SELECT TOP 25 MailFairTable.AccountNumber, MailFairTable.School,
MailFairTable.WhsePullDate, MailFairTable.WhseSheetPulled
FROM MailFairTable
WHERE (((MailFairTable.WhseSheetPulled)=0));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top