Access Query for Averaging based on Dates

1
votes

I have a table that looks like this: 

╔═════╦═══════════╦══════╦═══════╗
║ ID  ║ Attribute ║ Year ║ Month ║
╠═════╬═══════════╬══════╬═══════╣
║ 1   ║ 15.2      ║ 2014 ║ 11    ║
║ 1   ║ 13.1      ║ 2014 ║ 12    ║
║ 1   ║ 5.6       ║ 2015 ║ 1     ║
║ 2   ║ 7.9       ║ 2014 ║ 11    ║
║ 2   ║ 12.3      ║ 2014 ║ 12    ║
║ 2   ║ 45.6      ║ 2015 ║ 1     ║
║ 3   ║ 23.2      ║ 2014 ║ 11    ║
║ 3   ║ 45.7      ║ 2014 ║ 12    ║
║ ... ║ ...       ║ ...  ║ ...   ║
╚═════╩═══════════╩══════╩═══════╝

What I would like to do is average the "Attribute" for each ID over the last year, starting with the current month and year. For example, I might want to find the average of ID = 2 from June,2015 (6/2015) to June, 2014 (6/2014). I am trying to implement this using only a query (no VBA).

I have already been able to average the current year's "Attribute", but that only includes the months passed in this year, not the previous, and the real problem I am having is that the Year and Month are separated into two fields. If they were a date, this would have been trivial.

I have also been able to get the data for the current and previous years with this: 

SELECT Table.ID, Table.Year, Table.Month, Table.Attribute
FROM Table
WHERE
        (((Table.ID)="Some ID Number")
    AND ((Table.Year)=Year(Date())
     Or (Table.Year)=Year(Date())-1));

But again, I am stuck with the months and values for each. What is the best course of action? Is there a way to combine the Year and Month field into another query and do something with that (Just throwing out ideas, I'm pretty lost)?

2
sqldatems-access

2 Answers

1
votes

Maybe something like this will work:

Select id, avg(Table.Attribute) 
From Table
Where (Year*100 + Month) between 201406 and 201506
Group by id, (Year*100 + Month)
0
votes

Another approach would be to re-create a strong typed date using DateSerial in a derived table, which you can then use this to do the Group By Id and apply the Average aggregate:

SELECT x.ID, Avg(x.Attribute) AS AvgOfAttribute
FROM (
  SELECT MyTable.ID, MyTable.Attribute, DateSerial([Year], [Month],1) AS TheDate
  FROM MyTable
)  AS x
WHERE (((x.TheDate) >= '2014-06-01' And (x.TheDate) < '2015-06-01'))
GROUP BY x.ID;

Obviously, if you filter by a single ID then there is no need to apply the GROUP BY.