SQL's Top 5 statement......can the number be changed 2

[crpjaviman]

I have a database that needs to print out certain number of records from a query result. I have used the SQL-SELECT TOP 5 statement, but depending on the results, there may be 11 or 22 that need to be printed.

Is there any way of changing the "5" to a number count from the query?

 

[MichaelRed]

Hmmmmmmmmmmmmmmm,

It is easy enough to change the "5" to any value desired, but ........ what do you mean by " ... there may be 11 or 22 that need to be printed.

Is there any way of changing the "5" to a number count from the query? ... "? If you want all of the possible results, you don't want a "TOP" clause at all. If you have some deterministic process whcich tells you how many 'results' you want, it can be done but it is probably a bit messier than you will want to dabble in.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Katerine]

Hi crpjaviman,
I'm guessing that what you want is to look at the top five values of some field, and return all records with one of those values?

The first way I can think of to do this is to create a subquery, which just has that field, and groups by that field (so each value is returned only once), then does a Top 5, so it returns the top 5 values.

Then, in the main query, show the subquery. Locate the field you wish to filter on, and link it to the subquery. Leave the default of "only show records that are the same in both tables/queries"

Now, it should filter to those top 5 values.

Hope that helps. Hope that made sense.. It's way past my bedtime

 

[GComyn]

You could have the sql in code. Then you can set the number of records dynamically.... like so....

On a form, have a text field named 'txtRecords'.

Then have a command button to run the report.

In the reports Open Event have:

strSQL = "Select Top " & forms![FORM NAME]![txtRecords] & " [FIELD1], [FIELD2] " & _
         "From [MY TABLE]"
Me.Recordsource = strsql

This should work... it may need a little work, though...

GComyn

 

[crpjaviman]

Thanks all for the help.

GComyn,
I think this should work and will be trying it as soon as I can get off these other projects and continue on this one.

Thanks again all.