Select Top N Question 1

[smdemo]

I'm trying to make an append query to append a set amount of cases data to a sampling table. I am following MaCorr's sampling calculations which in my case leaves me with the formula of, 96.04/(1+(95.04)/[Number of records on my qry meeting criteria]).

Example would be if I have 185 records meeting my specified criteria I want to append 63 records to my destination table.

Right now I am running a select query to find the number of records then running an append query and manually entering the calculated sampling size in my select top N.

Any ideas here so I can automate this and eventually move the append query off to a macro?

Thanks
Steve

 

[HarleyQuinn]

If you're happy to use a bit of VBA this should be no problem. Is that a viable option for you?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[smdemo]

Hi Harley

I'm prepared to, I figured there is no other way around it. I'm just not sure where to put the vba since I don't have the query attached to a form or anything and how to solve for my variable N (the number of cases).

 

[HarleyQuinn]

You will be able to run the VBA from the macro.

I'm heading home now but if no-one else posts a solution for you before I get back I'll knock you some sample code up.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[smdemo]

Thank you Harley. What I've done since this morning is made a form that is linked to the query of interest and in a text box I perform my calculation needed (96.04/(1+(95.04)/Me.Recordset.RecordCount)so I now have my value n in that form. Now I only need to get that value of n into the select top n.

 

[HarleyQuinn]

Hi, sorry about the delay.

That's very useful steps you've made there, it makes my response a lot easier

What I would do is dynamically build the query in the code behind the form, something along the lines of:

Dim strSQL as String

strSQL = "SELECT TOP " & txtYourTextBox & " YourField FROM YourTable WHERE YourCriteria = 'YourVal'"

Then all you need to do is run the query based on the strSQL you've just built.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[smdemo]

Works great, thanks a bunch!

 

[HarleyQuinn]

You're welcome, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.