Top x records in query (user definable x??)

[PortyAL]

I have a query set to find the top x records in a query (and another for top x%), but x varies depending on the total number of records. Is it possible to enter x as a parameter each time the query is run, without having to go into the design view each time and changing it there?

Thanks

AL

 

[Hakala]

Can't you use the parameter query standard of asking a user question in the criteria row?

[How many records do you want?:]

If not, please post your SQL. Thanks!


Michelle Hakala

http://www.winebird.com/

 

[lespaul]

Actually I don't think you can use the Access parameter prompt for something like this. You will need to build a small form that asks the user 'How Many to return' with a button and then in the code of the ClickEvent build and run the SQL.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

You can do this with a very slow solution using DCount() or possibly a subquery. For example, open Northwind and use this SQL in a query:

SELECT Val(DCount("CustomerID","Customers","CustomerID <='" & [CustomerID] & "'")) AS TopNum, 
Customers.*
FROM Customers
WHERE (((Val(DCount("CustomerID","Customers","CustomerID <='" & [CustomerID] & "'")))<=Val([Enter Top Number])))
ORDER BY Customers.CustomerID;

I would quickly change the parameter query to reference a control on a form.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PortyAL]

Hi

Got it sorted by using VBA to prompt for the number of records and then letting it construct the query i.e.

Private Sub Command2_Click()
Dim strsql
Dim TopN

Set db = CurrentDb
Set qdf = db.QueryDefs("qry 3% of band")

TopN = InputBox("How many samples?")

strsql = " Select Top " & TopN & " [qry Invoices (no threshold)].ID, [qry Invoices (no threshold)].Type, [qry Invoices (no threshold)].Date, [qry Invoices (no threshold)].[Account Code], [qry Invoices (no threshold)].Year, [qry Invoices (no threshold)].Supplier, [qry Invoices (no threshold)].[Reference 3], [qry Invoices (no threshold)].Reference, [qry Invoices (no threshold)].Period, [qry Invoices (no threshold)].Gross, [qry Invoices (no threshold)].Random, [qry Invoices (no threshold)].From, [qry Invoices (no threshold)].To FROM [qry Invoices (no threshold)]WHERE ((([qry Invoices (no threshold)].Gross) Between [Lower Limit] And [Upper Limit] Or ([qry Invoices (no threshold)].Gross) Between -[Lower Limit] And -[Upper Limit]))ORDER BY [qry Invoices (no threshold)].Random;"

qdf.SQL = strsql

Set qdf = Nothing
Set db = Nothing

End Sub


Thanks

AL