Hi Any help appreciated.
I'm trying to write a query that shows BranchName, Sales and SalesDate for records where the Sales amount >= to the average sales amount for the branch ie. the average sales for Brighton is 237 so the record for Brighton where sales are 200 should be excluded.
To illustrate what I want I have pasted tables with basic data.
BranchID |
BranchName |
1 |
Brighton |
2 |
Crawley |
3 |
Worthing |
4 |
London |
5 |
Eastbourne |
BranchID |
SaleDate |
Sales |
1 |
2006-09-15 |
200 |
1 |
2006-10-13 |
300 |
1 |
2006-11-02 |
150 |
1 |
2007-04-01 |
300 |
2 |
2006-11-03 |
4000 |
3 |
2005-01-01 |
600 |
3 |
2005-02-02 |
900 |
3 |
2005-03-15 |
700 |
3 |
2006-05-16 |
900 |
5 |
2006-10-01 |
775 |
This code shows only branches whose average sales were greater than or equal to 300.
Code:
SELECT DISTINCTROW tbl_Branch.BranchName, Avg(tbl_BranchSales.Sales) AS AverageSales
FROM tbl_Branch INNER JOIN tbl_BranchSales ON tbl_Branch.[BranchID] = tbl_BranchSales.[BranchID]
GROUP BY tbl_Branch.BranchName
HAVING (((Avg(tbl_BranchSales.Sales))>=300));
How do I modify this code to show:-
BranchName, Sales and SalesDate for records where the Sales amount >= to the average sales amount for the branch ie. the average sales for Brighton is 237 so the record for Brighton where sales are 200 should be excluded.
Thanks
PBE