CODEWITHSUNDEEP
ms-accessaverage
falcs
falcs

Reputation: 509

Average values in a column across several rows for each unique value in another column in MS Access

I have query which produces a result like this:

ID | Area | Price
1 | A | 10
2 | A | 15
3 | A | 10
4 | B | 20
5 | B | 15

The query linked two tables one with ID and Area, another with ID and Price. I want change my query to average the price field for each area so I would end up with:

Area | Price
A | 11.6
B | 17.5

I have multiple price fields which I would like to average in the same format.

I'm using MS Access 2010.

Upvotes: 1

Views: 3948

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123619

You can achieve your desired result by simply basing your "averaging query" on your existing query. Say your existing query was saved as AreaAndPriceByID. Just create a new query like this:

SELECT Area, AVG(Price) AS AvgPrice
FROM AreaAndPriceByID
GROUP BY Area

If you want to round the averages to one decimal place you can use Round(AVG(Price), 1) instead.

Upvotes: 1

Related Questions