By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,558 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,558 IT Pros & Developers. It's quick & easy.

Damn sql problem again(sorry!)

P: 9
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!
Oct 17 '06 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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!
Oct 17 '06 #2

P: 9
Sorry yes it is in Access I completely forgot to say that!
Oct 17 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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!
Oct 17 '06 #4

P: 9
Thanks a lot I will try it as a form =)
Oct 17 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.