Results 1 to 7 of 7
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Sorting combobox items by most entered for last 3 months

    I have a combobox that I am wanting to make kind of fancy. Is there a way to add a query to sort the items that were tested by the most common items on the top of the combobox list. Each item tested has its own record and I can get it to sum all the items. I am having trouble with the 3 months part and getting it to display in the combobox in the order from most tested. Any help would be greatly appreciated on the direction to go and maybe a link to study.



    Thank you very much for your help,
    Walker

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Need to clarify: where or how does the 3 months part come into it?

    you have say 100 records to display in your combobox, of these 60 are in the last three months and the remaining 40 prior to that. You want to sort the 60 by most tested at the top and the remaining 40 by something else at the bottom

    or you have say 100 records to display in your combobox, all of which have been tested several times in the last year. You want to sort them by most tested in the last 3 months.

    or something else?

    Based on a table structured like this

    tblTests
    PK autonumber
    itemFK long
    testdate date

    I would suggest a query along these lines

    SELECT T.itemFK
    FROM tblTests T LEFT JOIN (SELECT ItemFK, Count(*) as qtrCount FROM tblTests WHERE testDate>dateadd("m",-3,date()) GROUP BY ItemFK) M3 ON T.itemFK=M3.itemFK
    ORDER BY qtrCount

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    or you have say 100 records to display in your combobox, all of which have been tested several times in the last year. You want to sort them by most tested in the last 3 months.
    This is what I meant. I have some items that are only tested once a year and could be at the bottom of the combobox list. just trying to make less scrolling.

    where or how does the 3 months part come into it?
    I just figured I could get a decent average for sorting by using the last 3 months worth of items. Also the 3 months will be from NOW() -3. I think, if I understand your query you already have that covered.

    Thank you Ajax!! I will work on adapting this to my situation. I will post results and marked solved as soon as I finish.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    only difference between date() and now() is the time - so if your query uses now() you will exclude records prior to the current time on the first day of the 3 month period

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Oh yeah! (*palms forehead*). Thanks.

  6. #6
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Ajax,

    SELECT T.itemFK
    FROM tblTests T LEFT JOIN
    What is the T for? This is something I have not seen before. I googled it but have not found an explaination.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It's an alias (same as M3 for the subquery). Just saves on typing in this case but otherwise useful when same table is used in different contexts in a query - (the access default is suffix the table name with _1 for the second iteration, _2 for the third etc)

    You can use ... AS T if you want but it is not required.

    or just change back to

    SELECT tblTests.itemFK
    FROM tblTests LEFT JOIN........ ON tblTests.itemFK=...

    So many times I see posters using really long table and query names and when they have complicated nested iifs or other calculations they become very difficult to read - so I tend to post with aliases to try to get them to see the benefits. Sometimes they get it, sometimes they don't. It's a habit I picked up writing in SQL Server.

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

Similar Threads

  1. Replies: 5
    Last Post: 08-16-2016, 07:15 AM
  2. Replies: 6
    Last Post: 08-04-2015, 08:42 AM
  3. Need help with sorting with months of year
    By gacapp in forum Reports
    Replies: 13
    Last Post: 07-23-2015, 11:58 AM
  4. Sorting Combobox?
    By Fionn in forum Access
    Replies: 6
    Last Post: 07-10-2013, 01:57 AM
  5. Sorting by combobox problem
    By wsurritte in forum Access
    Replies: 11
    Last Post: 02-28-2011, 02:11 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