Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40

    Unanswered: Calculate Quintiles in a Query

    I have a table with scores in it. I would like to seperate the table into quintiles A, B, C, D, E with equal counts per quintile based on the score. How can this be achieved?

    Thanks,
    Doug
    Doug

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    What I would do is first rank you records from 1 to whatever (10) then I would divide the number of records (count *) by 5 and depending on the ranking assigne and where it falls into place (which fifth) assign it an A,B,C,D,E.


    In the ms knowledgebase look for a file called "QrySmp00.exe" it has a sample DB that has several sample queries. One will show you how to rank you records. as far as the other parts, you are one you own. It is pssible to do what you are after, but it may take more then one query to do it.

    Depending of you skills with SQL and VBA, it may be faster to write a VBA code that does this for you and write the quintile back to the orginal table.

    S-

  3. #3
    Join Date
    Dec 2003
    Location
    Greenwood, IN
    Posts
    40
    I have them in a rank order by score desc. I was hoping there was an internal function in Access to do it. Can I do multiple SQL selects in an Access query? something like

    Select Top ((select count(*) from table)/5) * from table

    That would give me the top quintile. Then I could union in additional queries where and filter out the records already used.
    Doug

Posting Permissions

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