Solved

Radom Sample with criteria

Posted on 2004-04-09
39
348 Views
Last Modified: 2012-06-27
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
Comment
Question by:jennifer_borman
  • 16
  • 14
  • 4
  • +1
39 Comments
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10793768
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
 

Author Comment

by:jennifer_borman
ID: 10793798
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10793916
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 168 total points
ID: 10793930
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
 

Author Comment

by:jennifer_borman
ID: 10793983
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
 

Author Comment

by:jennifer_borman
ID: 10794023
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
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794031
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
 

Author Comment

by:jennifer_borman
ID: 10794163
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10794201
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
 

Author Comment

by:jennifer_borman
ID: 10794203
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
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794236
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
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794245
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
 

Author Comment

by:jennifer_borman
ID: 10794281
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
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794288
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
 

Author Comment

by:jennifer_borman
ID: 10794313
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
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794319
What other references do you haev checked?

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

Chris
0
 

Author Comment

by:jennifer_borman
ID: 10794342
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
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794360
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794410
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
 

Author Comment

by:jennifer_borman
ID: 10794427
"sample_level" is a field in the table that I use to determine the unit and can use as criteria for the count.
0
 

Author Comment

by:jennifer_borman
ID: 10794452
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
 

Author Comment

by:jennifer_borman
ID: 10794459
nevermind. found it
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794464
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
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794469
Did it work?
0
 

Author Comment

by:jennifer_borman
ID: 10794475
same error variable not defined
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794514
Ok, one more reference to try.

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

Chris
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794523
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
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 10794862
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
 
LVL 7

Expert Comment

by:brgivens
ID: 10803245
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
 

Author Comment

by:jennifer_borman
ID: 10806480
I tried the above SQL script and it is returning an error stating "invalid use of Null"
0
 
LVL 7

Assisted Solution

by:brgivens
brgivens earned 166 total points
ID: 10807605
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
 

Author Comment

by:jennifer_borman
ID: 10807749
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
 
LVL 7

Expert Comment

by:brgivens
ID: 10807778
Please post the actual SQL you are using
0
 

Author Comment

by:jennifer_borman
ID: 10808140
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
 
LVL 7

Expert Comment

by:brgivens
ID: 10808542
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
 
LVL 5

Assisted Solution

by:Krys_Wilson
Krys_Wilson earned 166 total points
ID: 10808857
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
 

Author Comment

by:jennifer_borman
ID: 10808892
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now