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

    Question Unanswered: Need Help Configuring Random Query?

    I have a table with RandomID, EmpID, CompanyID. Some Companies will have great than 25 employees. I need to setup a query where I can randomly select all employees from all companies whos employee count is less than 25.
    I have tried the following:
    SELECT top 10 percent Count(*), empID
    FROM tlbCompany
    Group By empID
    HAVING Count(*)<25
    ORDER BY rnd(RandomID);

    This counts the total number of employees, so if a company has 10 employees, it will only display one of the them.
    Can this query be done?
    And, what am I doing wrong?

    Thanks to all that help....
    Enviva..

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Eviva

    If you want the top 10% from EACH and for each company with less than 25 employees then I think you will have to use code - I don't think it can be done in SQL.

    Otherwise would this do the trick:

    Code:
    SELECT top 10 percent empID
    FROM tlbCompany INNER JOIN (SELECT CompanyID FROM tlbCompany HAVING Count(*) <25) AS [Comp25] ON tblCompany.CompanyID = [Comp25].CompanyID
    ORDER BY RandomID
    It selects the top 10 percent of employees from all the employees employed in companies with less than 25 employees. It is as random as your RandomID however the return will always be the same (excluding deletions\ aditions of course). If you want to select a random sample each time the query is run you'll need to supply some info about your RandomID - data type, way it is derived, upper and lower boundaries etc. Info about the EmployeeID might be useful too - this might be better for this purpose in fact.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi Eviva,
    Agree with pootle_flump: I reckon to get a 'fair' random number you will need to derive the employee id from a function. Sadly I can't find an equivalent of MySQL's RAND function inbuilt into JET SQL. I'm not certain if thats uniques to MySQL or part of ISO SQL.

    However you maybe able to fake it by limiting the top 'x' to be the random number and order by descending employeeID / surname or what ever other mechanism you want to order the result set and pick the first value returned.

    the first step would be to idenitfy companies with less than 25 employees and count the number of employees for each of those companies. This may have to be a subselect or child query.
    next use that as a feed for your limit clause, and order by descending.

    I'm not sure it will work - I think it may need a fair bit of jigging around, and i'm not sure it would be a truly random result, but it may suffice for your purposes.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    Hi Eviva,
    Agree with pootle_flump: I reckon to get a 'fair' random number you will need to derive the employee id from a function. Sadly I can't find an equivalent of MySQL's RAND function inbuilt into JET SQL. I'm not certain if thats uniques to MySQL or part of ISO SQL.
    Hi Healden

    RND can be used to get a random number - in fact, it has been used in answer to similar thread where the user wanted a random record from a table. However, you need to know the compostion of the column you will be randomly selecting from.

    I'll try to find that thread...

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh dear - search (such as it was) is dead too. Long live Search!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Hi Healden

    RND can be used to get a random number - in fact, it has been used in answer to similar thread where the user wanted a random record from a table. However, you need to know the compostion of the column you will be randomly selecting from.

    I'll try to find that thread...

    HTH
    I couldn't see any reference tyo that fuinction in the help. The rnd function within VBA fine. The RAND predicate in MySQL will return a random record from a recordset, which on the face of it is right up Enviva's street. But I cannot see any equivalent within JET/Access

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's just like the vb function IsNull - you can use it within Access SQL but it isn't native to JET.

    RAND sounds nice though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Posts
    67
    Pootle,
    I have given this a try, but I am getting hung-up on [Comp25], it just shows an empty table in design view.

    What is [Comp25] in relation to the tables and records?
    Thanks....

  9. #9
    Join Date
    Feb 2004
    Posts
    67
    Healdem,

    Could you help me setup a subselect of child query. I have never done that before.
    I have set up a randomizer in a Function and it seems to be working. It randomly selects all employees from all companies. Is there a way too just filter-out the companies that have 25 or more employees?

    thanks for your help....enviva

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Enviva
    Pootle,
    I have given this a try, but I am getting hung-up on [Comp25], it just shows an empty table in design view.

    What is [Comp25] in relation to the tables and records?
    Thanks....
    [Comp25] is a derived table - a sub query. It returns all the companies with less than 25 staff.

    Ok - try running

    Code:
     
    SELECT CompanyID FROM tlbCompany HAVING Count(*) <25
    If that doesn't return rows then you need to alter the SQL to match your table.

    Alternatives include:

    Code:
    SELECT top 10 percent empID FROM tlbCompany
    WHERE CompanyID IN(SELECT CompanyID FROM tlbCompany HAVING Count(*) <25)
    ORDER BY RandomID
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Posts
    67
    Pootle,

    I have tried the above code and I get and error: "You tried to execute a query that does not include a specified expression "CompanyID" as part of an aggregate function."

    Is there any code I could use to accomplish the same thing.

    thanks again for your help...enviva

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Oops

    Plop - you are correct soz

    Code:
     
    SELECT CompanyID FROM tlbCompany GROUP BY CompanyID HAVING Count(*) <25
    Code:
    SELECT top 10 percent empID
    FROM tlbCompany 
    
    
    WHERE CompanyID IN(SELECT CompanyID FROM tlbCompany GROUP BY CompanyID HAVING Count(*) <25)
    ORDER BY RandomID
    Code:
     
    SELECT top 10 percent empID
    FROM tlbCompany INNER JOIN (SELECT CompanyID FROM tlbCompany GROUP BY CompanyID HAVING Count(*) <25) AS [Comp25] ON tblCompany.CompanyID = [Comp25].CompanyID
    ORDER BY RandomID
    Ho Hum
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Posts
    67
    Pootle,
    THANK YOU SO MUCH.....that works just the way I needed it to. I am still learning......The hard way... "learn as you go"

    Thanks again.....Enviva

  14. #14
    Join Date
    Feb 2004
    Posts
    67
    Pootle,
    I thought everything was working. But now I get this error:
    =========================================
    The microsoft jet database engine cannot find the input table or query "SELECT CompanyID FROM tbl Company GROUP BY CompanyID HAVING Count(*)<25". MAke sure it exits and that its name is spelled correctly.
    =====================================
    If I go back into the query and change "anything", then the query works just fine. Then I go to place it in a form and I get the following error.
    =======================================
    The record source 'SELECT>>>>>>>.....; specified on this form or report does not exit
    you misspelled the name, or it was deleted or renamed in the database...(and so on)
    ========================
    Then if I try and run the query separately I again get the top{first} error. But if I go to the design view and again change something, it will work again, until I attach it to a subform or listbox in a form.

    Any thoughts on why I am getting this?
    Thanks again...enviva

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Enviva

    Access sometimes makes a hash of the SQL with derived tables. Try the using the one with the IN(...) statement instead.
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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