Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004

    Unanswered: summary query in access

    I am trying to sum up sales for employees and get the employee with the highest sales in one query. The query I have below works but it doesnt get me the EmployeeID. Assume all the fields are in the same table. If I try to do something like this it bombs on me: "MAX(SUM(OrderAmount)"

    SELECT Max(OrdersSum) AS MaxOrders FROM (SELECT SUM(OrderAmount) as OrdersSum
    FROM Orders
    GROUP BY EmployeeID);

    Thanks in advance!

  2. #2
    Join Date
    Dec 2004


    Let's do it...

    SELECT top 1 EmployeeID,Sum(OrderAmount) as [OrdersSum]
    FROM Orders
    Group by EmployeeID
    Order By 2 DESC

    Thats the Solution without Sub Queries... Can be done with subqueries too...

    And let me say you that there is a bug in this query... if you have "n" Employees with the same value... but this can be workarounded also...

    good work,


Posting Permissions

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