SQL stuck point

[Qwerty8989]

Hello all,

I am currently working on an sql query to find employees which handled more orders than average in a certain half year. For arguments sake the half year is august-dec1997. I will use two queries, one to calculate COUNT and one for the avg:

So far I have the COUNT query sorted:

SELECT Employees.EmployeeID, Count(Orders.EmployeeID) AS CountOfOrders
FROM Employees, Orders
WHERE (((Employees.EmployeeID)=[Orders].[EmployeeID]))
GROUP BY Employees.EmployeeID;

However, I am uncertain how to proceed from here and find the >AVG part of this query. Any help would be appreciated- thankyou


To add to this I think the next table should be like this:

SELECT qrycountOfOrders.EmployeeID, Employees.FirstName, Employees.LastName, qrycountOfOrders.CountOfOrders,
WHERE
FROM qrycountOfOrders, Employees
WHERE qrycountOfOrders.EmployeeID = Employees.EmployeeID;

But I am unsure where to put the >=AVG...

 

[ByteMyzer]

Try this one on for size. It uses a combination of subqueries and in-line queries in a single SQL statement that should give you what you're looking for:

SELECT Employees.FirstName & ' ' & Employees.LastName AS EmployeeID
FROM Employees INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID
WHERE Orders.OrderDate>=#8/1/1997#
AND Orders.OrderDate<=#12/31/1997#
GROUP BY Employees.FirstName & ' ' & Employees.LastName
HAVING Count(Orders.OrderID)>
 (SELECT Count(Orders_1.OrderID)
  FROM Orders AS Orders_1
  WHERE Orders_1.OrderDate>=#8/1/1997#
  AND Orders_1.OrderDate<=#12/31/1997#
 )/
 (SELECT Count(Employees_1.EmployeeID)
  FROM 
  (SELECT DISTINCT Orders.EmployeeID
   FROM Orders
   WHERE Orders.OrderDate>=#8/1/1997#
   AND Orders.OrderDate<=#12/31/1997#
  ) AS Employees_1
 );

 

[Qwerty8989]

Thankyou very much for that =)