Select data from a specific row 1

[IANGRAND]

I am trying to calculate the 98th percentile from some air quality data I have. The dataset is rather large consisting of 20,000 + values, growing daily. I have tried ranking the data, but this too long, in excess of 10 minutes.

The other method is to find the 19th highest value as this equates to 97.8 percentile. To do this I am sorting the data in descending order, knowing the value I want will be the 19th row. Does anyone know how I can automatically select the data contained in the 19th row of a table?

Cheers

Ian Grand

 

[lupins46]

select top 1 * from (Select top 19 * from mytable order by myfield desc) as T1 order by myfield asc

Put your own table and field names in.

 

[IANGRAND]

A new twist to the tale, rather than selecting the 19th row everytime, is it possible to use a number taken from a text box.. ie

say Textbox156 has the number 36 in it (this number will change from day to day)

Ive tried replacing 19 with [Textbox156], i assumed it would look up the figure

select top 1 * from (Select top 19 * from mytable order by myfield desc) as T1 order by myfield asc

 

[lupins46]

You have to edit the SQL of the query in code and then run the revised query.

For example, the following code runs when the user clicks a command button on the form that holds the textbox. Th textbox allows input of the number.
In this case the query is originally saved as Query34 and the form is called form16. The textbox is called text0.
You need to use your own names.


Dim strSQL
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("query34")

strSQL = "SELECT TOP 1 * FROM (Select top " & Forms!form16!Text0 & " * from orders order by freight desc) AS T1 ORDER BY freight;"
qdf.SQL = strSQL

Dim stDocName As String

stDocName = "Query34"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Set qdf = nothing

 

[IANGRAND]

Nope sorry im lost here.

I have a Textbox (Text156) which calculates the row number that I need to find using the following code:
=DLookUp("[CountOfExpr1]","COUNT_HOURLY_AVERAGE_DAYS")*0.0022

I then have a SQL query, which I am attaching to mousemove on Textbox157. This query is:

select top 1 * from (Select top 19 * from mytable order by myfield desc) as T1 order by myfield asc

But I want to replace 19 with the value currently found in Text156.

Cheers

Ian Grand

 

[lupins46]

How you are running this query. What is the code you are using?

The mousemove event seems a bit extreme - the event fires every time the mouse moves even a tiny amount. So the event will fire hundreds of times as you move across the textbox.

The main issue here is that the sql string must be built in code and then executed in a way that will display the results. The results of a query can only be displayed if the query is a saved Access query. SO it is necessary to build the sql string in code and then save it to an existing saved query and run that query.

 

[IANGRAND]

The code was in the last statement I produced. See above

Also the mousemove is on the textbox, rather than the whole form

 

[lupins46]

Sorry, but that code is not executable code. It can only be run as part of other code.

So how are you managing to run it?

 

[IANGRAND]

thorough a query and a macro on the mousemove