??how to?? 10 random records per user in large file

[Trixie]

Hello,

I am just starting to learn Access and know some basics. I've trie
using SELECT TOP * to try to get what I need, but obviously Access i
not reading my mind correctly wko

I'm working with database tables that have around 200,000 rows of dat
pertaining to work-units completed by 70 employees in a week (colum
label = ENTR). The SELECT TOP * brought me back records for only on
user...it was the right number of records though - 10 for emp# 45678

I need to randomly select 10 records for *EACH *user-id (unique to th
employee) so that I can review productivity and quality results.

Currently, I am doing this for 6 teams by exporting groups of data t
Excel and randomizing there, which is time consuming and becoming
pain.

Is this something that can be done in Access; keeping the explanatio
simple for the pre-school Access user -->me?

Thanks~

 

[Trixie]

Trixie,

I'm sorry, but I beg to differ. IMO, this is absolutely NOT a beginner
question!!! Intermediate, maybe, but beginner no way!!! I've been doing
this for a long time, and it took me a good half an hour to get this working
the way I wanted it to. Here is the code with hopefully liberal comments.
If you want, I can e-mail you the database and maybe save you some

headaches..



Hi PieterLinden,

...such a beginner I can't even ask "beginner" type questions cgrin

I didn't see any code attached to look at, but my natural curiosity
wants to take a look and try to learn and conquer. If you can attach a
db, that would be wonderful too!

Thanks.

 

[Stefan Hoffmann]

hi Trixie,

I'm working with database tables that have around 200,000 rows of data
pertaining to work-units completed by 70 employees in a week (column
label = ENTR). The SELECT TOP * brought me back records for only one
user...it was the right number of records though - 10 for emp# 45678

I need to randomly select 10 records for *EACH *user-id (unique to the
employee) so that I can review productivity and quality results.

Productivity should be a simple count:

SELECT user_id, COUNT(*)
FROM yourTable
GROUP BY user_id;

Quality per employee? I'm not sure if this makes sense, especially with
only 10 records. Normally I would take first a random sample lets say
1000 records.

Then you need to apply your quality measures.

Create a partition over these measures (good, bad and ugly).

Now I would group for my employees.

Currently, I am doing this for 6 teams by exporting groups of data to
Excel and randomizing there, which is time consuming and becoming a
pain.

Is this something that can be done in Access; keeping the explanation
simple for the pre-school Access user -->me?

Sure.

Picking random records:

http://www.mvps.org/access/queries/qry0011.htm

But as Pieter said, this is almost a intermediate++ question.

Getting ten random records is now down by a join query, e.g.

SELECT D.*
FROM Employee E
LEFT JOIN
(
SELECT TOP 10 I.*
FROM Data I
WHERE Randomizer() = 0
AND I.EmployeeID = E.EmployeeID
ORDER BY Rnd(IsNull(I.anyField) * 0 + 1)
) D


mfG
--> stefan <--

 

[pietlinden]

PieterLinden via AccessMonster.com;667633 Wrote:

Trixie,


headaches..

Hi PieterLinden,

..such a beginner I can't even ask "beginner" type questions cgrin

I didn't see any code attached to look at, but my natural curiosity
wants to take a look and try to learn and conquer.  If you can attach a
db, that would be wonderful too!

Thanks.

Must have used up all my brain cells trying to get the code to work
and forgot to save some for actually copying and pasting the code into
that big window... <g>

Option Compare Database
Option Explicit

'************ Code Begin ***********
'Code courtesy of
'Joe Foster
Private Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function
'************ Code End *************


Private Sub CollectRandomRecords(ByVal lngPatientID As Long)
Dim strSQL As String ' variable to hold the SQL string we're
building.

' build the SQL statement...
strSQL = "INSERT INTO RandomRecords ( ePatientID, StudyType,
StudyNumber, EnrollDate ) "
strSQL = strSQL & "SELECT TOP 10 Enrollment.PatientID,
Enrollment.StudyType, Enrollment.StudyNumber, Enrollment.EnrollDate "
strSQL = strSQL & "FROM Enrollment WHERE (((Enrollment.PatientID)
=" & lngPatientID & ") And ((" & Randomizer() & ") = 0)) "
strSQL = strSQL & "ORDER BY Rnd(IsNull(Enrollment.RecordID)*0+1);"

DBEngine(0)(0).Execute strSQL
End Sub

Public Sub GetRecords()
'PURPOSE: loops through the PatientID's in the table "Patient" and
calls "CollectRandomRecords" which selects
' a random set of 10 records for that PatientID and writes
them to the "RandomRecords" table.

Dim rsP As DAO.Recordset

'get a set of patientIDs from the 'Patient' table.
Set rsP = DBEngine(0)(0).OpenRecordset("SELECT PatientID FROM
Patient;", dbOpenForwardOnly)

' for each PatientID in the set, "collect" the randomly selected
records by appending them to a table....
Do Until rsP.EOF
' run the routine that generates the random set of records
(the Top values query) for this record's PatientID
' and appends the records to our holding table.
Debug.Print "Collecting records for " &
rsP.Fields("PatientID") & "..."
CollectRandomRecords rsP.Fields("PatientID")
' go to the next patient record
rsP.MoveNext
Loop

'cleanup
rsP.Close
Set rsP = Nothing

' just feedback so I know it's done...
Debug.Print "Mission Accomplished!!!"
'... or ...
MsgBox "Done creating Patient records!", vbOKOnly + vbInformation
End Sub

Far more complicated than it needed to be, right Stefan?

 

[James A. Fortune]

Sure.

Picking random records:

   http://www.mvps.org/access/queries/qry0011.htm

But as Pieter said, this is almost a intermediate++ question.

Getting ten random records is now down by a join query, e.g.

SELECT D.*
FROM Employee E
LEFT JOIN
(
   SELECT TOP 10 I.*
   FROM Data I
   WHERE Randomizer() = 0
   AND I.EmployeeID = E.EmployeeID
   ORDER BY Rnd(IsNull(I.anyField) * 0 + 1)
) D

mfG
--> stefan <--

Following your example, I tried:

SELECT tblEmployee.EID, RandomItems.Item FROM tblEmployee LEFT JOIN
(SELECT TOP 10 A.EID AS EID, B.Item FROM tblEmployee AS A,
tblEmployeeItems AS B
WHERE A.EID=B.EID
ORDER BY Rnd(EIID)) RandomItems ON tblEmployee.EID = RandomItems.EID;

where tblEmployee has EID as a primary key and tblEmployeeItems has
EIID as a primary key. I know that having distinct values for EID
causes the random number generator to reinitialize. I suspect that
simply having a field name within the argument, such as is the case
with your IsNull(anyfield) * 0 + 1 construction, is enough to reset
the randomizer (i.e., avoid the query optimization that replaces the
function call). Some sample queries I ran seem to support that
suspicion. My suggestion is to try your query without the
Randomizer() = 0 part.

James A. Fortune
(e-mail address removed)

 

[Stefan Hoffmann]

hi Pieter, James,

Just wondering.... did anybody solve this without resorting to recordsets? I
could not get the SQLs posted to work, no matter what I tried. I just wanted
to learn a new trick...

I think my single statement is simply not working. First of all, there
was the join condition missing and I wrongly used a dependent sub-query
as a join table.

You cannot do it in a single query. Sorry for wasting your time. Should
have tested it before posting.


mfG
--> stefan <--

 

[De Jager]

Hello,

I am just starting to learn Access and know some basics. I've tried
using SELECT TOP * to try to get what I need, but obviously Access is
not reading my mind correctly wko

I'm working with database tables that have around 200,000 rows of data
pertaining to work-units completed by 70 employees in a week (column
label = ENTR). The SELECT TOP * brought me back records for only one
user...it was the right number of records though - 10 for emp# 45678

I need to randomly select 10 records for *EACH *user-id (unique to the
employee) so that I can review productivity and quality results.

Currently, I am doing this for 6 teams by exporting groups of data to
Excel and randomizing there, which is time consuming and becoming a
pain.

Is this something that can be done in Access; keeping the explanation
simple for the pre-school Access user -->me?

Thanks~


--
Trixie

~TRIXIE
------------------------------------------------------------------------
Trixie's Profile: 438
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=186537

Excel Live Chat

 

[Stefan Hoffmann]

hi Pieter,

I was just wondering if it could be done purely in SQL, because I could not
figure it out. Can it be done with more than 1 query? I tried and found the
only way I could do it was with VBA. The question of how many queries isn't
really an issue.

You can't avoid the usage of VBA entirely.

My idea is quite simple (top-down):

SELECT *
FROM qryOrderNum
WHERE OrderNum<= 10


qryOrderNum:
SELECT *,
(
SELECT Count(*)
FROM tblRandomized I
WHERE I.RandomNo <= O.RandomNo
AND I.Group = O.Group
)
FROM tblRandomized O

with

SELECT *
INTO tblRandomized
FROM qryRandomized

and

qryRandomized:
SELECT ID, Group, Rnd(IsNull(ID)*0+1)) As RandomNo
FROM tableData
WHERE Randomizer()=0;


As you can see in qryOrderNum I need to access the randomized values
twice. Thus the temporary table to get the same [RandomNo]. This must be
done using VBA or a macro.

I think that you can introduce a RndRepeat() function to avoid the
helper table, e.g.

Public Function RndRepeat(AId As Long) As Double

'if AId exists then return stored value.
'otherwise create new Rnd() and store it in a mapping array
'or collection.

End Function

But you need here an additional resetting method in VBA.


mfG
--> stefan <--