SQL to show sales which are higher then average sales by branch

[PBE]

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.

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

[June7]

Build a query that calculates the average by Branch then join that query to Sales table.

[PBE]

Thank you for this. I now have it working!!