General discussion

Locked

Access Query - Creating a Sample

By audit ·
We have several queries that select a sample of records for us to review. We insert the random (rnd) function into a column and input the sample size we desire into the Top Values box on the Query Menu Bar. As we use the parameter query function extensively to pop up and ask for date value and the like, we have tried unsuccessfully to do the same with the Top Value. This means as the population varies and the statistical sample size we need changes, we have to open each query and change the Top Value and save the query and then run the report.

We want to incorporate these queries into a macro to faciliate the production of the sample reports created for each audit we perform. Is there a way to have the query ask for the sample size that goes into the Top Value box?

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access Query - Creating a Sample

by DKlippert In reply to Access Query - Creating a ...

In the Criteria box of the Query type:

[What is the Sample size?]
This is a parameter query. When it runs, an input box will collect the size entry.

You can also use Count(*) as a way to determine the number of records in the sample and use that as the Top Value.

Collapse -

Access Query - Creating a Sample

by audit In reply to Access Query - Creating a ...

If I understood the first part of the answer correctly, it didn't work. This is the way a normal parameter query works so we had also previously tried this approach.

I have included the Field information from the Design Grid for furtherinsight
Randomizer: Rnd([MLCus]![AcctNr])

I don't know for a fact but believe that the answer will have to be in the form of VBA code. I think this because according to the Access Help "You can't set this property (TopValues) in code directly. It's set in SQL view of the Query window by using a TOP n or TOP n PERCENT clause in the SQL statement." Therefore I believe that it would take the macro calling some type of Input Form. I am grasping at straws - Access just might have this functionality!

Collapse -

Access Query - Creating a Sample

by Bob Sellman In reply to Access Query - Creating a ...

Here's an idea. Don't know if it will work.

Define a function in a module in your application that might be called TopValue. Place in the function the code to ask the user to enter a value. You might even do other calculations within the function as needed against the top value.

In your query reference the function.

Collapse -

Access Query - Creating a Sample

by audit In reply to Access Query - Creating a ...

Poster rated this answer

Collapse -

Access Query - Creating a Sample

by timfox In reply to Access Query - Creating a ...

You could setup the new queries using VBA code. Unfortunately, you can't set the TopValue property of an established query - but you can set and save a query definition with the 'SELECT TOP nn' SQL format. You could then establish the sample size (the nn value) at run time.
Hope it helps
TimFox

Collapse -

Access Query - Creating a Sample

by audit In reply to Access Query - Creating a ...

Poster rated this answer

Collapse -

Access Query - Creating a Sample

by timfox In reply to Access Query - Creating a ...

I should have posted this code snippet...

Dim strGetSQL As String
strGetSQL = "SELECT TOP 10 ProductName AS TenMostExpensiveProducts, UnitPrice FROM Products ORDER BY UnitPrice DESC;"
Me.RecordSource = strGetSQL

Collapse -

Access Query - Creating a Sample

by audit In reply to Access Query - Creating a ...

Poster rated this answer

Collapse -

Access Query - Creating a Sample

by audit In reply to Access Query - Creating a ...

This question was closed by the author

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums