select an entry within a group that is closest to the group's average

[mlindaman1]

Hello - I have another question that is related to my last. I again have a table that has entries that include a location value, a measurement value, and a date value.

Location	Date	Measurement
6050	56731	30
6050	56731	50
6050	56731	150
5090	34700	20
5090	34700	50

This time, I need to create a query that will group the measurements by the Date and Location value, then select the entry within each group that is closest to the average. In the event that two or more measurements on the same date have the same absolute difference from the mean (such as when there are two values only), I need the query to select either one (it doesn't matter).

The following SQL is the closest I've come (ignore the use of restricted words in this example). It doesn't work for several reasons, namely the GROUP BY clause doesn't allow for the subquery in the aggregate function.

SELECT Table1.Location, Table1.Date, ABS(Table1.levVa - (SELECT AVG(Table1.levVa) FROM Table1)) AS AvgDevia
FROM Table1
GROUP BY Table1.Location, Table1.Date
ORDER BY AvgDevia ASC
LIMIT 1;

If anybody has any ideas here, it'd be very appreciated - been stuck on this all day.

[Micron]

You will probably have to do this in two steps: sort by location and date, get the average of the location/date grouping, then use the subquery approach in another query where this first query is the source.
that is closest to the average
I think you'll also have to decide if the closest number is to be greater than or lesser than the average and use either choice in the criteria, or else introduce another step. Maybe a query that has two calculated fields to get the Min value that is greater than the Avg and the Max value that is less than the Avg. Those values would be the criteria in the last query.

[NTC]

It is not clear to me as to whether you have used the Aggregate Query feature in the product (in query design view is sigma symbol looks like a big E). This should give you what you seek in that Average is one of the choices for a column. Use the default Group for the other 2 columns.