Hm! All the great plans of mice and men ...
I've been playing with prototype SQL and cannot get it to work simply with an intermediate result set. I encounter an 'Operation must use an updatable query' error (#3073). There is apparently the hope that this may be overcome by playing with the user permissions, but I don't think you want to bother with all that and anyway moving the db from one place to another probably means you have to repeat the task of setting permissions.
OK, all this means is the SQL solution is a little more clumsy (and perhaps a trifle slower) than hoped for because you need to create an intermediate table. Thus there are two discrete steps to achieving your requirement: run a Make Table query and then run an Update query. These steps could presumably be encapsulated in a macro (I'm not a macro person) with a Set Warnings Off if you prefer it. (Then there is always the VBA solution but we'll leave that for now.)
I understand that the span of years may be different from execution to execution. You therefore need a parameter; I have assumed the spread is always three years but if the spread differs then you will need two parameters. I have chosen the parameter to be the end date since this is where you want to store the average.
The Make Table query is similar to your SQL:
Code:
SELECT tblExample.ID, Avg(tblExample.NIbyA) AS AvgOfNIbyA, CLng([EnterEndDateAsYYYYMMDD]) AS TY INTO tblAverages
FROM tblExample
WHERE (((tblExample.TextYear) Between [EnterEndDateAsYYYYMMDD]-20000 And [EnterEndDateAsYYYYMMDD]))
GROUP BY tblExample.ID, CLng([EnterEndDateAsYYYYMMDD]);
I've called the table, 'tblAverages.' I cannot use Max(TextYear) since this returns the maximum for the whole table and not for the group; instead I convert the parameter to a number and call it 'TY' - it needs to be a number for linking in the Update query. The result set is restricted by the WHERE clause; subtracting 20000 from the end date calculates the start date. The second term in the GROUP BY list is probably redundant.
Here's the Update query.
Code:
UPDATE tblExample INNER JOIN tblAverages ON (tblExample.TextYear = tblAverages.TY) AND (tblExample.ID = tblAverages.ID)
SET tblExample.AvgNIbyA = [tblAverages].[AvgOfNIbyA];
Let me know how you get on.