Results 1 to 8 of 8
  1. #1
    johnwick111 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4

    Display Top value queries and multi layer results

    Hi everyone !

    I am facing difficulties in displaying my Top 5 values in the query result. In my table called "Customers", I have 2 fields, "Names" and "price" with 10 records.

    Select Customers.name, Customer.price
    From Customers
    ( This shows all the 10 records)
    I am trying to show only the top 5 highest price in the query result, any idea how I can do that?



    Another question is I have another table called "Stores", I have 2 fields. "Store_name" and "ratings" . I am planning to show the top 5 ratings for each Store_name, so the query will be group by Store_name and each Store_name will show the top 5 highest ratings out of the many records. any idea how can I achieve that? Thanks !

    Select Stores.store_name, Stores.ratings
    From Stores
    Group by Stores.store_name
    (​This shows all the records)

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1.Replace SELECT …. with SELECT TOP 5....

    2.This requires a Top N per group approach using a subquery. See http://allenbrowne.com/subquery-01.html#TopN
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    johnwick111 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4
    Thanks for the reply!

    1. Is there a formula that filter the top 5 records? [Select Top 5] display the first 5 records instead of the highest top 5


    2. Following the allenbrowne example,

    Select Stores.store_name, Stores.ratings
    From Stores
    Where Stores.ratings IN
    (Select Top 5 Stores.ratings
    From Stores AS Dupe
    Where Dupe.store_name = Stores.store_name
    Order by Dupe.ratings DESC)
    Order by Stores.store_name

    I get all the results instead of only top 5 for each store_name.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post a copy of your database or supply us with some sample data.

    You may get some ideas from this recent post on Top N.

  5. #5
    johnwick111 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4
    Hi, database is as attached. For query 1 that i have tried, how do i make each different store_name only show the top 5 ratings instead of all the results? thanks !
    Last edited by johnwick111; 03-19-2019 at 09:04 AM.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this

    Code:
    SELECT Stores.store_name
        ,stores.ratings
    FROM Stores
    WHERE Stores.id IN (
            SELECT TOP 5 dupe.id
            FROM Stores AS Dupe
            WHERE Dupe.store_name = Stores.store_name
            ORDER BY Dupe.ratings DESC
                ,dupe.id
            )
    ORDER BY stores.store_name
        ,ratings DESC;
    store_name ratings
    Apartment 100
    Apartment 100
    Apartment 98
    Apartment 98
    Apartment 97
    Guest suite 96
    Guesthouse 96
    Hotel 74
    House 100
    House 99
    House 99
    House 99
    House 97
    Loft 99
    Townhouse 99
    Townhouse 98
    Townhouse 96
    Townhouse 95

  7. #7
    johnwick111 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4
    That's correct ! Thanks for the help !

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2017, 04:29 PM
  2. 2nd layer subreport not displaying
    By danyd in forum Reports
    Replies: 1
    Last Post: 09-26-2013, 06:08 PM
  3. Trying to do a multple-layer select in a form
    By adreasler in forum Forms
    Replies: 4
    Last Post: 08-22-2013, 02:15 PM
  4. Replies: 3
    Last Post: 05-02-2013, 10:36 AM
  5. Multi Layer Form
    By macftm in forum Forms
    Replies: 4
    Last Post: 05-06-2012, 09:59 AM

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