Random records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TUNSTAD - 07 Dec 2005 15:38 GMT

Please can anyone help?

Right then, I have a table with students records that tracks name, address,
teacher, etc....

I want to be able to create a query that lists 20% of each Teachers current
students for a questionnaire.

I am using the following Module to generate random students:

Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function

I am using the following to generate 20% of students:

SELECT TOP 20 PERCENT [Application Form].*, [Application Form].[Trainee
Status], [Application Form].Trainer
FROM [Application Form]
WHERE ((([Application Form].[Trainee Status])=2) AND (([Application
Form].Trainer)=2) AND ((randomizer())=0))
ORDER BY Rnd(IsNull([Application Form.Surname])*0+1);

Getting 20% of all current students is fine, but I want 20% of each Teachers
Current Students??????

For example, If Teacher 1 has 30 students I want to list 6 students
If Teacher 2 has 40 students I want to list 8 students

Hope you can help.
Thanks

Reply to this Message

John Spencer - 08 Dec 2005 13:15 GMT

You will need to use a sub-query to do this.

What field do you have that identifies a student specifically? What field
do you have that identifies a Trainer specifically?
As an example, I will use StudentID and TrainerID

The following is untested and may not give you the correct results. The
random function may be the confusing factor since every time the subquery
runs a new set of random numbers will be generated.

SELECT Distinct A.*
FROM [Application Form] as A
WHERE A.StudentID IN
(SELECT TOP 20 PERCENT B.StudentID
FROM [Application Form] as B
WHERE B.[Trainee Status]=2
AND B.Trainer=2 <---if this is TrainerID, then remove
it
AND randomizer()=0
AND B.TrainerID = A.TrainerID
ORDER BY Rnd(IsNull(B.[Application Form.Surname])*0+1)

> Please can anyone help?
>
[quoted text clipped - 33 lines]
> Hope you can help.
> Thanks

Reply to this Message