Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Question Unanswered: Can 2 random queries work?

    Hi all,

    I am not real sure whether or not this can be done with a query, but here goes.
    I have a query randomly selecting the top 6 from a selected group.

    SELECT TOP 6 tblConsortium.ContactID, tblConsortium.RandomID, patients.LastName & ", " & Patients.Firstname AS [Employee Name], Contacts.Company AS [Company Name]
    FROM patients INNER JOIN (Contacts INNER JOIN tblConsortium ON Contacts.ContactID=tblConsortium.ContactID) ON patients.PatientID=tblConsortium.PatientID
    WHERE (((tblConsortium.ContactID) In (SELECT ContactID FROM tblConsortium GROUP BY ContactID HAVING Count(*) <25))) And (((tblConsortium.PatientID)=Randomizer())=0)
    ORDER BY Rnd(IsNull(tblConsortium.patientID)*0+1);

    What I would like to do now is: The randomly selected Employees need to have a randomly selected TEST. There are only 3-Test to choose from. I have placed the TESTS in a separate table and entered 20 of TEST1, 4 of TEST2, and 1 of TEST3. I then set a query randomly shuffling these items. I need these TESTS to randomly be assigned to the randomly selected Employees above.

    I would greatly appreciate any thoughts or help…
    Thanks Enviva

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Hmmm ... Since you have 25 employees ... I'd do this differently. I'd query all 25 employees and randomly assign an order number AND assign the random test number THEN pick the top 6 order count ....
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    MOwen,
    I need that query, because I am randomly selecting from all companies w/less than 25 employees. As companies hire/fire throughout the year, they will fall in/out of the criteria. Currently I have 45 companies and 12 of them have over 25-emp. I have a separate query that will randomly select from a specific company w/> 25 emp.
    Your info would work on my "Separate Qry", can you tell me how to randomly assign as order number?
    Thanks,,,,enviva

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Enviva
    MOwen,
    I need that query, because I am randomly selecting from all companies w/less than 25 employees. As companies hire/fire throughout the year, they will fall in/out of the criteria. Currently I have 45 companies and 12 of them have over 25-emp. I have a separate query that will randomly select from a specific company w/> 25 emp.
    Your info would work on my "Separate Qry", can you tell me how to randomly assign as order number?
    Thanks,,,,enviva
    Whoa there ... Where did having multiple companies come into this equation? Also, are you pulling 6 employees from ANY of the companies for the test (versus pulling 6 employees from EACH company for the test)?

    As for your question: you can't do that within a query itself ... What I'm talking about is some VBA code ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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