Results 1 to 8 of 8
  1. #1
    dillopk is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6

    How to find "top n" most frequently occurring values in a simple table?

    I am an Access newbie tracking parking lot use, I have a simple table where each record contains 2 fields I care about, the date a car was observed in the lot and the car's license plate number. There are records from many different dates and many different cars. Some cars park there every day, some only once in a while. How can I show the plate number that was found on the most dates, second most dates, etc.? For example, plate XYZ123 parked there on 20 days, ABC567 parked there 18 days, etc. The idea is to determine who parks there most often, second most often etc. to get a "Top 10" or "Top 50" or whatever number report. The lot is only checked once daily so no car is found more than once on the same date, if that matters. Is there a function or combination of functions that can do this for me?

  2. #2
    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

  3. #3
    dillopk is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6
    I experimented with TOP but only got back the first 5 or 10 or whatever license plates regardless of how often they occurred. A friend suggested using GROUP BY somehow, I'm looking for details online.

  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
    You could count(datesfield) and Group By (platenumber)
    then Order by Count(dates ) descending
    to get all your data in a list

    When you post an issue with a query, please post the query SQL so readers have some context. That is, show us what you tried.

  5. #5
    dillopk is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6
    Thanks, I will fumble around with that (I wasn't kidding when I said Newbie!) As far as posting my code, details, etc. there aren't any, I had no clue where to start to get this done. My training and experience consists of reading a library book and creating a couple of tables. Wish me luck and thanks for your guidance.

  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
    Consider this mock up re show the top 3 most observed Plate Numbers and count of observations.


    Table Plates
    Code:
    ID plate obsDate
    1 ABC234 23-May-2016
    2 XRY888 23-May-2016
    3 ABC234 24-May-2016
    4 XRY888 27-May-2016
    5 ABC234 25-May-2016
    6 ABC234 28-May-2016
    7 ABC234 29-May-2016
    8 ABC234 30-May-2016
    9 ABC234 31-May-2016
    10 ZMT776 29-May-2016
    11 QED123 28-May-2016
    12 QED123 03-Jun-2016
    13 QED123 04-Jun-2016
    14 XRY888 04-Jun-2016
    15 XRY888 05-Jun-2016
    16 XRY888 07-Jun-2016
    17 ABC234 07-Jun-2016
    18 ABC234 08-Jun-2016
    19 ABC234 09-Jun-2016
    20 XRY888 10-Jun-2016
    Query:
    Code:
    SELECT top 3 Plates.plate
    , Count(Plates.obsDate) AS CountOfobsDate
    FROM Plates
    GROUP BY Plates.plate
    ORDER BY Count(Plates.obsDate) DESC;
    Result:
    Code:
    plate CountOfobsDate
    ABC234 10
    XRY888 6
    QED123 3

  7. #7
    dillopk is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2016
    Posts
    6
    THANK YOU that is exactly what I want and in a format I can understand. The example really helps.

  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: 3
    Last Post: 07-07-2016, 12:22 PM
  2. Replies: 8
    Last Post: 03-16-2016, 10:11 AM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 5
    Last Post: 11-19-2014, 08:47 AM
  5. Replies: 4
    Last Post: 12-03-2010, 04:05 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