------ Register to get rid of these "In Post" ads! ------


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

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

------ Register to get rid of these "In Post" ads! ------


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..

------ Register to get rid of these "In Post" ads! ------


Hi PieterLinden,

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

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.

__________________
~Trixie

------ Register to get rid of these "In Post" ads! ------


hi Trixie,

On 11.03.2010 04:17, Trixie wrote:
> 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 <--

------ Register to get rid of these "In Post" ads! ------


On Mar 11, 6:18*am, Trixie <Trixie.47n...@thecodecage.com> wrote:
> PieterLinden via AccessMonster.com;667633 Wrote:
>
> 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
>
> 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.
>
> --
> Trixie
>
> ~TRIXIE

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?

------ Register to get rid of these "In Post" ads! ------


On Mar 11, 8:10*am, Stefan Hoffmann <ste...@ste5an.de> wrote:

> 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
MPAPoster@FortuneJames.com

------ Register to get rid of these "In Post" ads! ------


James A. Fortune wrote:
>> Sure.
>>
>[quoted text clipped - 19 lines]
>> 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
>MPAPoster@FortuneJames.com

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...

Thanks!
Pieter

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

------ Register to get rid of these "In Post" ads! ------


hi Pieter, James,

On 13.03.2010 05:53, PieterLinden via AccessMonster.com wrote:
> 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 <--

------ Register to get rid of these "In Post" ads! ------


Stefan Hoffmann wrote:
>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 <--

Stefan,

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. Thanks for answering.

Pieter

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201003/1

------ Register to get rid of these "In Post" ads! ------



"Trixie" <Trixie.47n3jj@thecodecage.com> wrote in message
news:Trixie.47n3jj@thecodecage.com...
>
> 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
>
> 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:
> ??how to?? 10 random records per user in large file
>
> Excel Live Chat
>