Thread: Selective Random testing...
07-23-03, 14:34 #1Registered User
- 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!"
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
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
MsgBox "Employees Selected! Click VIEW REPORT to view employees"
intRnd = ""
intRndHi = ""
intRndLo = ""
07-23-03, 14:58 #2Village Idiot
- 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!
MarkInspiration Through Fermentation
07-23-03, 17:15 #3Registered User
- 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..