Top n records

[Guest]

I have the following query which selects the top 25 records based on the
"Random" field.

SELECT TOP 25 [qry Invoices].ID, [qry Invoices].Period, [qry
Invoices].Gross, [qry Invoices].Random
FROM [qry Invoices]
ORDER BY [qry Invoices].Random;

The problem is that I do not always want the top 25. Ideally I would like
the query to ask me how many records I want to show. Is this possible?

 

[JohnFol]

The query prompts for parameters that are missing,. This is used as a
shortcut by many developers not wanting to create a form to capture and
validate the entries properly. (Also, bear in mind that forms/reports show
data not queries) In your case, the top value is not a parameter so you will
need to a) prompt for the value, b) write a tiny bit of code.

For example, if you had a text box to get the value, you can compose the SQL
statement like this

strNewSQL = "SELECT TOP " & me.txtTopHowMany & " ProductID, ProductName
FROM Products"

Depending on what you do with the query, you use the above SQL.

For example, Me.Recordsource = strNewSQL

or myQuerydef.SQL = strNewSQL

Regards

 

[Guest]

Sorry. I'm a relative newbie to Access, and that went a little over my head!

Are you saying to put the SQL statement in a text box in the report and then
refer to it in the query?

Forgive my ignorance!!

The query prompts for parameters that are missing,. This is used as a
shortcut by many developers not wanting to create a form to capture and
validate the entries properly. (Also, bear in mind that forms/reports show
data not queries) In your case, the top value is not a parameter so you will
need to a) prompt for the value, b) write a tiny bit of code.

For example, if you had a text box to get the value, you can compose the SQL
statement like this

strNewSQL = "SELECT TOP " & me.txtTopHowMany & " ProductID, ProductName
FROM Products"

Depending on what you do with the query, you use the above SQL.

For example, Me.Recordsource = strNewSQL

or myQuerydef.SQL = strNewSQL

Regards

I have the following query which selects the top 25 records based on the
"Random" field.

SELECT TOP 25 [qry Invoices].ID, [qry Invoices].Period, [qry
Invoices].Gross, [qry Invoices].Random
FROM [qry Invoices]
ORDER BY [qry Invoices].Random;

The problem is that I do not always want the top 25. Ideally I would like
the query to ask me how many records I want to show. Is this possible?

 

[JohnFol]

Not quite. If the data is displayed in a Report, I presume you have a button
that opens the report.
Could you just describe exactly how you open the report ?

Sorry. I'm a relative newbie to Access, and that went a little over my
head!

Are you saying to put the SQL statement in a text box in the report and
then
refer to it in the query?

Forgive my ignorance!!

The query prompts for parameters that are missing,. This is used as a
shortcut by many developers not wanting to create a form to capture and
validate the entries properly. (Also, bear in mind that forms/reports
show
data not queries) In your case, the top value is not a parameter so you
will
need to a) prompt for the value, b) write a tiny bit of code.

For example, if you had a text box to get the value, you can compose the
SQL
statement like this

strNewSQL = "SELECT TOP " & me.txtTopHowMany & " ProductID, ProductName
FROM Products"

Depending on what you do with the query, you use the above SQL.

For example, Me.Recordsource = strNewSQL

or myQuerydef.SQL = strNewSQL

Regards

I have the following query which selects the top 25 records based on the
"Random" field.

SELECT TOP 25 [qry Invoices].ID, [qry Invoices].Period, [qry
Invoices].Gross, [qry Invoices].Random
FROM [qry Invoices]
ORDER BY [qry Invoices].Random;

The problem is that I do not always want the top 25. Ideally I would
like
the query to ask me how many records I want to show. Is this possible?

 

[Guest]

At the minute I have a report designed based on the query in my original
post. I haven't set up a menu form with buttons yet as I'm still in the
process of designing the overall project.

I'm opening the report from the main database window at the minute.

The project is an audit sampling application. "Qry Invoices" extracts a
sub-set of the total data, and assigns a random number to each record
selected. The query below then selects (at the minute) the top 25 records
based on the random number field - hence a random sample. However 25 may not
be a big enough sample, therefore I would like to be able to input the number
or percentage of the records to be chosen. The sampled items are then
displayed in the above report, which is an audit test matrix.

Hope this clarifies the situation

Thanks

AL

Not quite. If the data is displayed in a Report, I presume you have a button
that opens the report.
Could you just describe exactly how you open the report ?

Sorry. I'm a relative newbie to Access, and that went a little over my
head!

Are you saying to put the SQL statement in a text box in the report and
then
refer to it in the query?

Forgive my ignorance!!

The query prompts for parameters that are missing,. This is used as a
shortcut by many developers not wanting to create a form to capture and
validate the entries properly. (Also, bear in mind that forms/reports
show
data not queries) In your case, the top value is not a parameter so you
will
need to a) prompt for the value, b) write a tiny bit of code.

For example, if you had a text box to get the value, you can compose the
SQL
statement like this

strNewSQL = "SELECT TOP " & me.txtTopHowMany & " ProductID, ProductName
FROM Products"

Depending on what you do with the query, you use the above SQL.

For example, Me.Recordsource = strNewSQL

or myQuerydef.SQL = strNewSQL

Regards


I have the following query which selects the top 25 records based on the
"Random" field.

SELECT TOP 25 [qry Invoices].ID, [qry Invoices].Period, [qry
Invoices].Gross, [qry Invoices].Random
FROM [qry Invoices]
ORDER BY [qry Invoices].Random;

The problem is that I do not always want the top 25. Ideally I would
like
the query to ask me how many records I want to show. Is this possible?