Results 1 to 8 of 8
  1. #1
    maratkz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8

    Question Query form and restrict it by TOP5

    I want to select the TOP5 most popular products and their sales volume in terms of quantity and dollars on a monthly basis

    How can I do that creating a search form that is linked to a query and every time I select for example "May" it will show the top5 products for May???


    is there any same thing anyone has done before? Could you guide me please....

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    see if this is of any use to you

    http://allenbrowne.com/ser-62.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    maratkz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Thanks for the reply. I didn't get how to that still... Maybe I can explain better. So, at first, I create a list button of months button in the form and redirect it to a query that counts how many times the given product was ordered in a month. How can I make it that when I roll the list, like choosing: April, it will give me the Top 5 products in April????

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Create a query starting SELECT TOP 5...
    Use a combo with two columns to select the month. Bound column with month numbers 1-12 and second visible column with month names.
    Filter the query so the date range has Month(YourDateField)= Forms!FormName.ComboName

    Do you also need to specify the year as selecting May would include May 2018 etc as well as this year.
    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

  5. #5
    maratkz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    wow, thanks for the detailed procedure.
    Where can I filter the Month... is it in SQL or design?

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Query or VBA
    If using a query, put that in the query filter criteria for that date field ...or write an equivalent SQL statement and run as vba procedure
    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

  7. #7
    maratkz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    8
    Many thanks! I've done it successfully. You're a life saver haha great job

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome
    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

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

Similar Threads

  1. Replies: 6
    Last Post: 07-13-2015, 06:21 PM
  2. Restrict Access to Form Based on fOSUserName()
    By ricker090 in forum Programming
    Replies: 1
    Last Post: 11-28-2012, 04:07 PM
  3. Restrict user to navigation form
    By Whelt in forum Access
    Replies: 3
    Last Post: 04-22-2012, 06:19 PM
  4. Replies: 1
    Last Post: 03-13-2010, 12:32 PM
  5. Restrict form access to certain people
    By DannyBoy7783 in forum Forms
    Replies: 2
    Last Post: 06-09-2008, 06:18 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