Select top N values from table...and prompt for N

[scifibum]

Hi all,

Just getting started with Access, and I want to be able to select the top N records from a table.

I can build the query in Access design view just fine to pull some fixed number of records.

What I want to do, though, is have the N value be a parameter so the user can specify each time the query how many records they want to see.

Is there a way to set up a parameter prompt for the number of records to pull in a "Select Top N" query?

Or would I need to look into using VBA to construct the SQL? I think I can do that easily enough, but I'd like to know the options.

Thanks very much for any assistance.

 

[Norie]

Is there a way to set up a parameter prompt for the number of records to pull in a "Select Top N " query?

As far as I know there isn't.

I think you will have to use the VBA route.

 

[smozgur]

scifibum,

If it is ok to have a autonumber field in your table and also ok to sort the result by this autonumber field then it is possible.
Method doesn't require an inceremented field (there might be deleted records between two numbers), it simply creates an incrementing number field in query (which could be hidden) and uses a criteria as parameter to enter the quantity.

SQL query sample : ID is your autonumber field, tablename is the table name:

SELECT tablename.*
FROM tablename
WHERE ((((Select Count (*) FROM [tablename] as Temp
WHERE [Temp].[id] < [tablename].[id])+1)<[Quantity Please]+1));

If your query needs to be sorted by another field and also if you cannot have an ID autonumber field then this won't work and I agree with Norie.

Suat

 

[scifibum]

Thank you both kindly for the replies.

Unfortunately I need to sort the query by a date field, so I cannot use smozgur's idea.

However, I believe I was able to find a way to accomplish this without VBA

I created one query called "qryWeeks" which simply gives me a unique list of values in my date field:

SELECT DISTINCT tblWeekly_Agg.Period_Ending
FROM tblWeekly_Agg;

I then adapted the Numbered Query example here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=NumberedQuery.mdb

This gave me a query called "qryWeeksSequence" as in the following:

SELECT qryWeeks.Period_Ending, CLng(DCount("Period_Ending","qryWeeks","Period_Ending >= #" & qryWeeks!Period_Ending & "#")) AS Sequence
FROM qryWeeks
ORDER BY qryWeeks.Period_Ending DESC;

Finally I created a parameter query which allows me to select the N records that have a "Sequence" number that is less than or equal to the parameter input:

SELECT qryWeekSequence.Sequence, tblWeekly_Agg.*
FROM qryWeekSequence INNER JOIN tblWeekly_Agg ON qryWeekSequence.Period_Ending = tblWeekly_Agg.Period_Ending
WHERE (((qryWeekSequence.Sequence)<=CLng([How many?])));

The setup is really not any easier than the VBA route would have been, but I now have a way to prompt for the "Top N" records sorted on a date field, without using any VBA.

Now...Can someone help me figure out if there is a way to combine this into one query? The SQL is a bit mysterious for me (I did all my queries in the design grid then just copied the SQL from Access's SQL view).

Thanks again for the responses!!!