Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Radom Sample with criteria

I have a table with 20k+ records. The records can be seperated into 6 different business units, "c", "d","e","f","g",or "h". Every month I have to extract a certain number of records for a random sample from each business unit to send surveys to. In this table are people that we have already sent surveys to, but have a date populated in the "date_mailed" field. Currently, we are using SPSS syntax to extract the samples. I would rather bring it back into Access so everything is housed in one spot. I would like all the random sample records to be in one table so that I can do mail merges and queries off of it.

Any thoughts?
0
jennifer_borman
Asked:
jennifer_borman
  • 16
  • 14
  • 4
  • +1
3 Solutions
 
Krys_WilsonCommented:
Random number generator.

This question has previously answered a similar question.

http://www.experts-exchange.com/Databases/MS_Access/Q_20882869.html

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

Chris
0
 
jennifer_bormanAuthor Commented:
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.
0
 
nico5038Commented:
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
nico5038Commented:
Your date_mailed field will have to be added in the queries, but I guess we'll also need to be able to reset the date as otherwise you'll run out of records...

Nic;o)
0
 
jennifer_bormanAuthor Commented:
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.
0
 
jennifer_bormanAuthor Commented:
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.
0
 
Krys_WilsonCommented:
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
0
 
jennifer_bormanAuthor Commented:
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.
0
 
nico5038Commented:
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)
0
 
jennifer_bormanAuthor Commented:
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"
0
 
Krys_WilsonCommented:
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
0
 
Krys_WilsonCommented:
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
0
 
jennifer_bormanAuthor Commented:
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.
0
 
Krys_WilsonCommented:
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
0
 
jennifer_bormanAuthor Commented:
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?
0
 
Krys_WilsonCommented:
What other references do you haev checked?

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

Chris
0
 
jennifer_bormanAuthor Commented:
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
0
 
Krys_WilsonCommented:
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
0
 
Krys_WilsonCommented:
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
0
 
jennifer_bormanAuthor Commented:
"sample_level" is a field in the table that I use to determine the unit and can use as criteria for the count.
0
 
jennifer_bormanAuthor Commented:
Microsoft Visual Basic for Applications Extensibility 5.3 (or earlier version if you have it)
I don't have this listed in any version
0
 
jennifer_bormanAuthor Commented:
nevermind. found it
0
 
Krys_WilsonCommented:
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
0
 
Krys_WilsonCommented:
Did it work?
0
 
jennifer_bormanAuthor Commented:
same error variable not defined
0
 
Krys_WilsonCommented:
Ok, one more reference to try.

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

Chris
0
 
Krys_WilsonCommented:
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
0
 
Krys_WilsonCommented:
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
0
 
brgivensCommented:
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.
0
 
jennifer_bormanAuthor Commented:
I tried the above SQL script and it is returning an error stating "invalid use of Null"
0
 
brgivensCommented:
jennifer,

It does work against a test database I created in Access 2003.  What version of Access are you using? Does the SQL run if you remove the "WHERE IsNull(date_mailed)" line?
0
 
jennifer_bormanAuthor Commented:
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.
0
 
brgivensCommented:
Please post the actual SQL you are using
0
 
jennifer_bormanAuthor Commented:
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?
0
 
brgivensCommented:
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.
0
 
Krys_WilsonCommented:
Good Im glad someone knew another way to do this.  I got lost when looking through the DAO recordsets.

Thanks for helping out Brgivens!

Chris
0
 
jennifer_bormanAuthor Commented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 14
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now