Query to select values according to ID and date, then average

[mlindaman1]

Hello everybody -



I'm new to using SQL, and am trying to create a query or VBA routine. I have a database of many measurements which are associated with a location (numeric value) and a date (julian days). For example -

Location	Date	Measurement
4000	6025	20
4000	6025	40
4000	6050	70
6000	6025	30

There are some 90,000 of these measurements. As you notice, there are multiple locations and some locations have multiple measurements per year. What I'm trying to do is assemble a new table where there is only one measurement per year per location, with an average value taken if there is more than one measurement for that year at any particular location.

So, the after running the query/routine, a new table would be spit out that looks like this:

Location	Date	Measurement (Average)
4000	6025	30
4000	6050	70
6000	6025	30

So, essentially, if there's only 1 measurement at a location for a year, it would just be moved over to the new table unchanged. If there are 3 measurements at one location, but only two were from the same year, then the two would be averaged and brought over and the other would be moved over as a separate entry.

Sorry that this is so specific. Maybe a For/Next statement in VBA would be appropriate? Thanks for any help

[Micron]

As far as I can see, a simple totals query should do what you want. Group by Location and Year, and average the measurement field (choose average as the aggregate function).
BTW, Date is a bad name for a table. See this link http://allenbrowne.com/AppIssueBadWord.html

Never mind; I see it's not that simple. Will dig deeper.

Well, it helps to enter the data samples in a test table rather than the expected values . So it is that simple. Here's a sql for it (change to your table/field names):

SELECT tblJulian.Location, tblJulian.Dte, Avg(tblJulian.Measurement) AS AvgOfMeasurement
FROM tblJulian
GROUP BY tblJulian.Location, tblJulian.Dte;

[mlindaman1]

Awesome, worked perfectly!

Thanks - I'll have to brush up on how GROUP BY statements work.