top 30 records programaticaly

[cpampas]

Hi,
I am not sure If I am looking at this the right way. I want to get the highest number of the field "closecot" of qryCotacoes, considering only the n last entered records, in this case n would be a parameter.

I understand that in a select query I would set field parameters with :

n=30

Set MyQryDef = db.QueryDefs("qryCotacoes")                                             
MyQryDef.Parameters("Parameter1").Value = n

but how can I set programaticaly the top value parameter , wich is this case is 30 to a variable ?

SELECT TOP 30 *
FROM tblCotacoes
ORDER BY tblCotacoes.cotacaoid DESC;

I would appreciate your help

 

[theDBguy]

Hi. You would modify the SQL property. For example:

n=30
strSQL = "SELECT TOP " & n & " FieldList FROM TableName"
MyQryDef.SQL= strSQL

Hope that helps...

 

[cpampas]

Hi,
That realy helped, I am closer now. I do get the last n records(30) with the following :

strSQL = "SELECT TOP " & n & " closecot FROM tblCotacoes"
MyQryDef.SQL = strSQL

but of those records I would like to get the highest one, so I did this instead :

strSQL = "SELECT TOP " & n & " Max(closecot) As H FROM tblCotacoes"
MyQryDef.SQL = strSQL

hoping that I would get the highest of the 30 records, but I get the highest of all records in that table.
Any thoughts ?

 

[theDBguy]

Hi,
That realy helped, I am closer now. I do get the last n records(30) with the following :

strSQL = "SELECT TOP " & n & " closecot FROM tblCotacoes"
MyQryDef.SQL = strSQL

but of those records I would like to get the highest one, so I did this instead :

strSQL = "SELECT TOP " & n & " Max(closecot) As H FROM tblCotacoes"
MyQryDef.SQL = strSQL

hoping that I would get the highest of the 30 records, but I get the highest of all records in that table.
Any thoughts ?

Hi. Do a search on "Top N Per Group" subquery.

 

[CJ_London]

try

strSQL = "SELECT TOP " & n & " closecot FROM tblCotacoes ORDER BY closecot Desc"

note if two records have the same closecot value then you will get 31 records back because the records returned are based on the top values, not a recordcount. Rationale is quite simple, if you ask for the top 1 and there are 3 records with that value, which should it choose since the records are otherwise random after the initial sort? The usual way round it is to order by a secondary field, typically a primary key or timestamp

 

[Isaac]

Remember that using top and specifying a sort order go hand in hand.

It never makes sense to use TOP without specifying a SORT, unless you want to get back something random.

Edit: As CJ has mentioned +1

 

[cpampas]

Many thanks, it´s working just fine

Set MyQryDef = db.CreateQueryDef("")

strSQL = "SELECT TOP " & n & " closecot FROM tblCotacoes ORDER BY closecot Desc"
MyQryDef.SQL = strSQL

Set rs = MyQryDef.OpenRecordset(dbOpenDynaset)
rs.MoveFirst: HighNumber = rs!closecot
rs.MoveLast: LowNumber= rs!closecot

 

[Isaac]

Glad to hear things are working. A mention of one possible approach to saving 'template' queries, with placeholders, placeholders to be Replace()'ed at runtime:

Select TOP n records

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...

www.access-programmers.co.uk