Radom Sample with criteria

Random number generator.

This question has previously answered a similar question.

https://www.experts-exchange.com/questions/20882869/Random-Number-Generator.html

Let me know if you don't really understand that.

Chris

I am not too fluent on that type of coding. I am better with SQL. Plus that is not seperating a table into different sections and taking a sample of each of the sections. That is the hurdle that I am struggling with. In addition to the criteria that the "date_mailed" field has to be null.

Does my answer in:
https://www.experts-exchange.com/questions/20499721/random-sample-with-a-twist-from-ms-access-2000.html?query=random+nico5038&searchType=topic

gives you enough directions ?

Nic;o)

I read the description of what the VB is supposed to do, and yes that is the goal I am aiming for. I guess my first question would be, where do I put all that scripting. I haven't played with VB in Access yet.

Ok. I re-read this. I want to be sure that I have this straight:
1) In the SQL view I create a query stating "select distinct user from tblYours"
2). create the 2 update queries for the random number
3). that is where I get shadey.

Ok, let me see if I can help you with it.

Create a module called basGenerateRandom

Place this code in the module.  (Note this is assuming ADO recordsets)

************************************
Function GenRnd()
On Error GoTo HandleErr

Dim rst As New ADODB.Recordset
Dim LngRecCnt As Long
Dim strPrev As String
Dim I As Long
Dim intRnd As Long
Dim strID As String

LngRecCnt = DCount("ID", "YourTable")
strID = ""

rst.Open "SELECT * FROM YourTable", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rst.MoveLast
Debug.Print LngRecCnt * 0.05

For I = 0 To LngRecCnt * 0.05
    'goto a random number from within the range
    intRnd = Int((LngRecCnt - 2) * Rnd + 1)
    Do While InStr(strPrev, CStr(intRnd)) <> 0
        intRnd = Int((LngRecCnt - 2) * Rnd + 1)
    Loop
    strPrev = strPrev & "," & intRnd
    rst.AbsolutePosition = intRnd
    If strID = "" Then
        strID = rst.Fields("vccID")
    Else
        strID = strID & "," & rst.Fields("vccid")
    End If
   
Next

CurrentDb.Execute "Insert Into SurveyTable(ID) SELECT ID FROM YourTable where ID IN (" & strID & ")"

ExitHere:
    Set rst = Nothing
    Exit Function
HandleErr:
    MsgBox "RoutineName: Runtime Error:" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description
    Resume ExitHere
   
End Function
*************************************
What this does is grab the TOTAL number of records in your table, then the SQL statement uses the where statements to bring back only valid survey recipients.  Then randomly determines who to call and then places them into a SurveyTable.

Notes:
- Replace "SELECT * FROM YourTable" and replace with a SQL statement that has the WHERE clauses you need.  IE WHERE Date_Mailed Is Null

- Replace "LngRecCnt * 0.05"  with the percentage you need for your surveys in decimal form.

- Replace Debug.Print rst.Fields("ID") with the unique ID for your table.

HTH and Ill check back to see if you could follow that

Chris

I need to modify this to indicate a certain number of records based on "unit". So unit "c" may need 40 records for surveys to get out and unit "d" may only need 15. This is a predetermined and fixed number.

In that question I guided the questionner, thus making sure the code was understood well enough to maintain it later.
So the first steps were rather "global".
When your rows per unit are based on the number of tests, you might appreciate the "percentage" approach taken in that question, otherwise an additional field will have to be placed in your BusinessUnit table to work with and to allow the user to modify it when needed.

Nic;o)

I am also getting an error when I try to test it. It is erroring on the statement "Dim rst As New ADODB.Recordset". It is a compile error that says "User defined type not defined"

Well, there are several ways to do that as well.  If you are going to be pulling this from a form, make a Combo Box that has a list of All the Units.  Dim two more variable (Dim strUnit as string and Dim strPercent as long) then add this above the for loop.

Select Case Unit
    Case "A"
        strPercent = 0.01
    Case Else
        strPercent = 0.005
End Select

Then change "For I = 0 To LngRecCnt * 0.005" to

For I = 0 To LngRecCnt * strPercent

Chris

That means that you don't have the ADO reference checked.

In Tools>References

Make sure that Microsoft ActiveX Data Objects 2.1 library is checked (This is with Access 2000)

Chris

rst.Open "SELECT * FROM [monthly master list] where [monthly master list].[date survey mailed] is null", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rst.MoveLast

undefined variable for CurrentProject.Connection

This is why I assigned top points for this, because I am not good with the coding.

Shoot - Forgot this part for the SELECT CASE statement.  It should look like this:

strUnit = Forms!YourForm!YourCbo

Select Case strUnit
    Case "A"
        strPercent = 0.01
    Case Else
        strPercent = 0.005
End Select

Sorry about that!

Chris

No problem for the omission, but I am not using a form. Do I need to create one to make this work? Can I not do something that would say "if sample_level = ANW then LngRecCnt = 50"? And just list that type of thing for each unit?

What other references do you haev checked?

Check Tools>References again and all the checked ones will be at the top.

Chris

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library
Microsoft ActiveX Data Object 2.1 Library

I am working in 97...sorry should have indicated that from the beginning

Nope, you don't need a form to call this.  You can call it from the Debug window with Call GenRnd() or from a macro with RunCode > GenRnd()

If the number is always going to be absolute then you won't need a percentage.  So we can get rid of the Dcount.  The only thing I will need to know is where is sample_level going to come from?  Is that from the table?

Chris

Try adding:

OLE Automation and
Microsoft Visual Basic for Applications Extensibility 5.3 (or earlier version if you have it)

Then test by typing this in the immediate window (debug window)
?CurrentProject.Connection

Chris

"sample_level" is a field in the table that I use to determine the unit and can use as criteria for the count.

Microsoft Visual Basic for Applications Extensibility 5.3 (or earlier version if you have it)
I don't have this listed in any version

nevermind. found it

Ahh ok, then we will need to add the criteria to the DCount and also add the criteria to the SQL statement that opens the recordset.

Dcount("ID","Monthly Master List", "Sample_Level = 'a' ") ' Gives total number of records by Unit

"SELECT * FROM [monthly master list] where [monthly master list].[date survey mailed] is null and sample_level = 'a' "

Chris

Did it work?

same error variable not defined

Ok, one more reference to try.

Find Microsoft OLE DB ActiveX Data Objects. and then try the ?CurrentProject.Connection again.

Chris

Nevermind, thats not going to work either.  I think we have to use DAO.  Im not very good at it but its not that different from ADO.  Let me give it a try.

Chris

Jennifer,

I'm about to go home here and I am having trouble with a DAO recrodset.  I know it can be done but I have only worked with ADO.  The same principle applies though.

When I get home, Ill try and tackle this then as well.  I really hate to leave you hanging here.

Chris

TRANSFORM First(primaryID)
SELECT bizUnit
FROM hugeTable
WHERE IsNull(date_mailed)
GROUP BY bizUnit
PIVOT Int(x * Rnd(Right(primaryID,2)))

where x = # of records you want in each category

caveat:  if the number of records in any bizUnit is << x, this query may or may not return x records for that bizUnit - you may want to use a value of x > # records you actually need, or just re-run the query if the first execution doesn't return enough records.  My guess is that with 20k+ records, the query should work just fine as is.

I tried the above SQL script and it is returning an error stating "invalid use of Null"

I fixed the script. I didn't change the "primaryid" to match the field name that I have. It is giving me a crosstab, but there is no data in it other than the Unit name.

Please post the actual SQL you are using

TRANSFORM First([monthly master list].surveyid) AS FirstOfsurveyid
SELECT [monthly master list].sample_test
FROM [monthly master list]
WHERE (((IsNull((([monthly master list].[date_mailed]))))<>False))
GROUP BY [monthly master list].sample_test
PIVOT Int(30*Rnd(Right(surveyid,2)));
 
for 97 version
I fixed another "primaryid" that I had missed. Sorry about that. The script seems to work except that every so often there is a blank field. What would cause that?

I'm glad you got it to work :)

The blank fields are due to the Int(30*Rnd(Right(surveyid,2))) expression.  What this does is randomly select an identity between 0 and 29 for each record... there's always a chance that no records within a group will be assigned one (or more) of the identities.  There's even a (miniscule) chance that all records in the group will be assigned the same identity.

As I mentioned in my original post, the way around this is to either use more identities, say 35 in this case, and pick out the first 30, or to run the query twice if necessary.

It seems to be giving me what I need. My issue now is that this is supposed to be linked up to a mail merge and it would be easier actually for me if it was not in a crosstab format. Is there a way for it to give me the detail rather than having it in a crosstab?