SQL Access Query

[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.

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

 

[hernantorres23]

Hi, you can try this

SELECT tblSales.branchId, tblCities.branchName, tblSales.salesDate, tblSales.sales, DAvg("sales","tblsales","tblsales.branchid=" & [tblcities].[branchid]) AS promedio
FROM tblSales INNER JOIN tblCities ON tblSales.branchId = tblCities.branchId
GROUP BY tblSales.branchId, tblCities.branchName, tblSales.salesDate, tblSales.sales, DAvg("sales","tblsales","tblsales.branchid=" & [tblcities].[branchid])
HAVING tblSales.sales>=DAvg("sales","tblsales","tblsales.branchid=" & [tblcities].[branchid])


promedio

branchId	branchName	salesDate	sales	promedio
1​	Brighton	13/10/2006​	300​	237.5
1​	Brighton	01/04/2007​	300​	237.5
2​	Crawley	03/11/2006​	4000​	4000
3​	Worthing	02/02/2005​	900​	775
3​	Worthing	16/05/2006​	900​	775
5​	Eastbourne	01/10/2006​	775​	775

 

[PBE]

Thank you