Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003

    Unanswered: Selective Random testing...

    With help from experts from this forum.. I have learned alot.. Many things I never knew that you could do to make things easier..

    SO now I have come back in seek of some more guidance..

    I posted this question before.. got some help.. had an expert explain to me but i'm still stuck.. heres my problem

    I have a random database with roughly over a thousand employees to be randomly drug tested.. In this database.. 21 random employee get picked a month to get tested.. once each of these employees get tested.. they are stamped with current date.. these 21 picked employees will not get picked again for another 2 years from current date.. I made a field to add two years to the date they were first tested on...

    Everything is good.. I presented my database to the user... they use it.. and happen to pick alot of employees from the same department.. soo users get complaints.. then i get a call.. asking can i change this...

    I have had help on this subject from a user here.. he gave me some instructions. i tired.. but still nogo.. I'm stuck !

    So we came up with a way to change this.. since i do not have which department these employees are in.. I figure since I have the supervisor of each employees name.. can't i just use that to say...

    when i make a first pass to get 21 employees.. is there a way i can compare the superviors last name... if there is a match in supervisors last name.. then pick next employee until i get 21 employees that do not have the same supervisors last name...

    Sounds simple.. but I have been stuggling.. I new so bare with me.. and thanks for your patients..

    heres the code:::

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click
    Dim intRnd, intRndHi, intRndLo As Integer
    Dim strSQL, strCurrent As String
    Dim lpBuff As String * 25 ' Set Up NTUSER NAME
    Dim UserNameLong As Long ' Set Up NTUSER NAME
    Dim NTUserName As String ' Set Up NTUSER NAME
    UserNameLong = GetUserName(lpBuff, 25) ' Set Up NTUSER NAME
    NTUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) ' Set Up NTUSER NAME
    NTUserName = "" & NTUserName ' Set Up NTUSER NAME
    strCurrent = Date
    MsgBox "Press OK to Select Employee"
    For i = 1 To 21
    Me.RecordSource = "SELECT COUNT ([Name]) AS NoName FROM Table1 WHERE [NextTestDate] < #" & strCurrent & "# OR ISNULL([Tested]);"
    If Me![NoName] = 0 Then
    MsgBox "Warning! No Records Selected!"
    Exit Sub
    intRndLo = 1
    intRndHi = Me![NoName]
    intRnd = Int((intRndHi - intRndLo + 1) * Rnd + intRndLo)
    strSQL = "SELECT * FROM Table1 WHERE [NextTestDate] < #" & strCurrent & "# OR ISNULL([Tested]) ORDER BY [Name];"
    Me.RecordSource = strSQL
    DoCmd.GoToRecord acDataForm, "frmRandom", acGoTo, intRnd
    Me![Tested] = Format(Date, "Short Date")
    Me![NextTestDate] = DateAdd("yyyy", 2, Me![Tested])
    Me![UpdatedBy] = NTUserName ' Set Up NTUSER NAME
    End If
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Next i
    MsgBox "Employees Selected! Click VIEW REPORT to view employees"
    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command0_Click

    intRnd = ""
    intRndHi = ""
    intRndLo = ""

    End Sub

    AGain Thanks


  2. #2
    Join Date
    Jul 2003
    So much for random testing...

    No code help here, just some advice. What you've asked for is possible, and not all that hard. However, I think you should revisit this with your customer!

    Be very careful when developing systems that can become legal nightmares for your customer, they will come back to haunt you!
    A good lawyer would be able to trash a company drug policy like this one, becuase it is not random! I suspect a large dept got hit more often than a dept with 5 employees, and that dept is the one complaining. That's how random sampling works!

    What happens if...

    A new supervisor, with the same last name as another is hired?
    Create a supervisor table, and give each supervisor a unique id #,
    take the supervisor name out of the employee table, and use the supervisor ID instead.

    If your testing period is monthly, as it is in many companies, what happens to supervisors with less than 24 employees?
    That supervisor will run out of employees to be tested before the 2 year waiting period is up. His/Her employees are guaranteed to be tested once every 2 years, while employees of larger depts may only get tested
    once every 6 years. It won't take long for the employees in the smaller
    dept's to complain/file suit.

    I hope I don't come across as a jerk. I'm just trying to look out for a fellow developer!

    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2003
    you are definitely right.. ! These users they want and want.. but they don't know what they really are getting theirselves into.. What is the point of random drug testing if your gonna pick who you test ??!! Wierd i say..

    I will take your advice and consult the user of consequences for doing this..

    Thanks alot


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts