Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2010
    Posts
    10

    Unanswered: Find mode values in query

    Hi,

    First time post, so sorry if this isn't as clear as it should be - I'll try my best!

    I have a table which lists information on Employee (Name), Hours Worked (Hours) and the task they were doing at the time (Task). I need to design a query that will pull up one record for each employee with whichever task they perform most frequently.

    For example, if EmployeeA performs Task1, Task2, Task3, Task1, Task1, then the query will list:
    EmployeeA, Task1

    At the moment I've been experimenting with subqueries and count() and SELECT TOP, but to no avail. Any help would be great.

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is most easily achieved with a join to a derived table. I don't have time to write this but can guide you. One question though - what happens in the event of a tie?

    First step: create a query that lists all employees, all their tasks and a count for each task (or sum of hours performed for each task if this is how you are ranking them).

  3. #3
    Join Date
    Apr 2010
    Posts
    10
    Thanks for your response. I've gotten as far as getting a query that will list the values as you said. Having a GroupBy set on the employee and Task fields, then a Count on the task field as well yields the following:

    EmployeeA, TaskA, 4
    EmployeeA, TaskB, 2
    EmployeeA, TascC, 1
    EmployeeB, TaskB, 7
    EmployeeB, TaskC, 4
    EmployeeB, TaskA, 2
    EmployeeC, TaskC, 9

    ...etc. I'm stuck at trying to filter out all the other records and just leave the top of each employee. Using TOP 1 I can get the top for EmployeeA, but leaves me nowhere with any of the others!

    The other added complication is that the tasks are stored as numbers (TaskID) which join to a tblTasks table which has the tasks and the numbers listed. Ie:

    1, Task A
    2, Task B
    3, Task C

    I have found that if I get the above query working (with the count being ranked with most at the top), and run another query that does a First() for each Count() I can get each employee with the count of how many times they have done the task, but am stuck and getting the actual Task into the query as well - as soon as I include that (the ID or the Name of the task) I get multiple listings for each Employee.

    Am I asking the impossible here? My last thought was with some nested SELECT statements, but got a little bamboozled by the whole thing.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No it's all fine. Slow down a bit though - I'll get you there.

    So you have the query to answer my question - can you post the SQL please?
    Also, please let me know your answer about what happens when there is a tie.

  5. #5
    Join Date
    Apr 2010
    Posts
    10
    Thanks for your help - it is much appreciated.

    In terms of a tie it is really unimportant which one is chosen - in the end it will probably be across about 15+ records, so the likelihood of a tie is quite high. It's only being used as a "for your interest" purpose, it isn't an integral part of the operation.

    At the moment my SQL is looking like the following:

    SELECT EmployeeID, TaskID, Count(TaskID) AS CountOfTaskID
    FROM JobHours
    GROUP BY EmployeeID, TaskID
    ORDER BY Count(TaskID) DESC;

    Purpose of it being to group the records by employee and task, and count the number for each task, then rank them in reverse order to have the most frequent at the top. Just not sure how to remove the remainder of the records and just leave the top one for each employee. The resulting dataset is looking very much like the example I have above (with multiple records for each employee).

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Cool. I've taken your query, adjusted it a bit and created a derived table, joining it back to the original data.
    How does the below look?
    Code:
    SELECT JobHours.EmployeeID, JobHours.TaskID
    FROM JobHours
    INNER JOIN 
    (SELECT EmployeeID, MAX(Count(TaskID)) AS CountOfTaskID
    FROM JobHours
    GROUP BY EmployeeID, TaskID) AS counts
    ON JobHours.EmployeeID = counts.EmployeeID
    GROUP BY JobHours.EmployeeID, JobHours.TaskID
    HAVING Count(TaskID) = CountOfTaskID

  7. #7
    Join Date
    Apr 2010
    Posts
    10
    It looks good, and seems like it should work. However, Access is giving me attitude over the line:

    MAX(Count(TaskID))

    Im trying to nest another subquery in there, but its giving me a bit of a headache

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - it won't work - it is wrong.

    Code:
    SELECT JobHours.EmployeeID, JobHours.TaskID
    FROM JobHours
    INNER JOIN 
        (SELECT EmployeeID, Max(CountOfTaskID) AS MaxCount
        FROM 
            (SELECT EmployeeID, Count(TaskID) AS CountOfTaskID
            FROM JobHours
            GROUP BY EmployeeID, TaskID) AS counts
        GROUP BY EmployeeID) AS maxCounts
    ON JobHours.EmployeeID = maxCounts.EmployeeID
    GROUP BY JobHours.EmployeeID, JobHours.TaskID
    HAVING Count(TaskID) = MAX(MaxCount)
    Please give more details than "giving me attitude" if there is still an error. You know - like the error message
    Last edited by pootle flump; 04-19-10 at 06:10.

  9. #9
    Join Date
    Apr 2010
    Posts
    10
    Just had another thought - if the Inner Join is performed across the EmployeeID field, will it still give what I want? The primary key of the JobHours table is just a sequential ID number generated by access labelled JobHoursID. Since the JobHours table will contain many records for each employee, across many different dates, how will the join return the correct record which contains the task ID I need? Hopefully you didnt just answer that in your recent post!

  10. #10
    Join Date
    Apr 2010
    Posts
    10
    Hrmm, it's now saying that there is a syntax error in the JOIN clause - I'm just trying to find what it doesnt like. It is highlighting the "AS counts" part, but I dont see anything wrong with it...

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's my fault - I'm air coding whilst working on something else. I've edited the query.

  12. #12
    Join Date
    Apr 2010
    Posts
    10
    I took out both subqueries and ran them together (without the main query part) and they ran fine, even with the "AS counts" in there, so Im still unsure what the issue is. It really seems like it should be working...my SQL is just not good enough to figure out why it isnt

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You ran the edited query? Because there was an error.

  14. #14
    Join Date
    Apr 2010
    Posts
    10
    Sorry no I hadn't - now I have. Too may posts all at once! It's all looking good except for

    HAVING Count(lngzTaskID) = MaxCount

    says it isn't included as part of an aggregate function.

    Thanks again for the help - I hope it doesnt mess up your other work

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try now

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •