Damn sql problem again(sorry!)

[lafrion]

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. I know it would be something like this:

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


Any help would be appreciated- thankyou!

[MMcCarthy]

Is this SQL query in Access?

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. I know it would be something like this:

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


Any help would be appreciated- thankyou!

[lafrion]

Sorry yes it is in Access I completely forgot to say that!

[MMcCarthy]

This will give you the average:

SELECT Avg(qrycountOfOrders.CountOfOrders) AS AvgOfCountOfOrders
FROM qrycountOfOrders;

The easiest thing to do would be to establish this result as a control on a form. For this example lets call the form AverageOrders and the control AverageCount.

SELECT qrycountOfOrders.EmployeeID, Employees.FirstName, Employees.LastName, qrycountOfOrders.CountOfOrders,
FROM qrycountOfOrders, Employees
WHERE qrycountOfOrders.EmployeeID = Employees.EmployeeID
AND qrycountOfOrders.CountOfOrders > [Forms]![AverageOrders]![AverageCount];

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. I know it would be something like this:

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


Any help would be appreciated- thankyou!

[lafrion]

Thanks a lot I will try it as a form =)