此查询将使用系统表生成每个月的薪资：

PARAMETERS 
    [DateStart] DateTime, 
    [DateEnd] DateTime;
SELECT 
    Salary.employee_id, 
    Dozen.N AS Id, 
    DateAdd("m",[Dozen].[N],[DateStart]) AS DateMonth, 
    Salary.amount
FROM 
    (SELECT DISTINCT Abs([id] Mod 12) AS N FROM MSysObjects) AS Dozen, 
    Salary
WHERE 
    ((Dozen.N) <= DateDiff("m",[DateStart],[DateEnd])) 
    AND 
    ((Salary.effective_from) = 
        (Select Top 1 T.effective_from 
        From Salary As T 
        Where 
            T.employee_id = [Salary].[employee_id] 
            And 
            T.effective_from <= DateAdd("m",[Dozen].[N],[DateStart]) 
        Order By 1 Desc)))
ORDER BY 
    Salary.employee_id, 
    DateAdd("m",[Dozen].[N],[DateStart]);

输出：

现在，将此查询用作聚合查询计算平均金额的源，如：

SELECT 
    Salaries.employee_id, 
    Avg(Salaries.amount) AS AverageAmount
FROM 
    Salaries
GROUP BY 
    Salaries.employee_id;

在ms access中，您需要一个日历表。对于这样的表，可以使用如下查询：

select avg(amount)
from (select c.*,
             (select top 1 t.amount
              from [table] as t
              where t.effective_from <= c.date and
                    t.employee_id = @employee_id
              order by t.effective_from desc
             ) as amount
      from calendar as c
      where c.date >= @date1 and c.date <= @date2
     ) c