Results 1 to 7 of 7
  1. #1
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283

    TOP 10 Percent

    Hello,

    I'm trying to grab the top 10 percent from each ID from a table in a select query. And for some reason it is only grabbing the top 10 percent of like 4 of the ID's..Idk why. Here is the code:




    SELECT TOP 10 PERCENT [Table1].*
    FROM Table1
    ORDER BY [Templates], [ID];

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    If you are trying to take the top 10 percent of IDs shouldn't the order by line be the other way round?

  3. #3
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    I mean Im trying to grab the top 10 percent of each template. Each template has their own unique ID.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need to use a subquery. See here: http://allenbrowne.com/subquery-01.html

  5. #5
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Sounds like the ID repeats more than once in the table and you have more than 4 different IDs? If so, then add a GROUP BY on ID, Template so you will get the different IDs up to 10. And from what I read, I agree with Andy that ID should be ORDER first, unless you are looking for different Templates? Please provide more information if not on track yet.

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Code:
    SELECT field1, fieldID, Quantity
    FROM tbldata
    WHERE FIELDID IN (
        SELECT TOP 5 PERCENT fieldID
        FROM tbldata as S
        WHERE S.field1 = tbldata.field1
        ORDER BY quantity DESC
    );

    this sort of thing worked for me

  7. #7
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283
    There are 18 different templates in my table..I want 10 percent of each template pulled as a sample. And I figured doing it by the ID would help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Best way to store a percent value?
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 06-07-2015, 07:29 AM
  2. Percent problems
    By rice1973 in forum Programming
    Replies: 1
    Last Post: 09-02-2014, 05:36 PM
  3. percent
    By blackstatic42 in forum Access
    Replies: 4
    Last Post: 06-02-2013, 10:28 PM
  4. Negative percent on a report
    By bcoots in forum Reports
    Replies: 2
    Last Post: 01-05-2012, 03:05 PM
  5. Auto Updating Percent
    By walter189 in forum Reports
    Replies: 1
    Last Post: 09-07-2011, 01:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums