All

I have 3 tables : Hr, Hr_Salary_History and Plan_Year. Please see attached for Structure/sample contents & required output. (need the final salary based for each year specified in the Plan_Year table).

Thanks
N

I have re-created your tables exactly as your Spreadsheet and answer is as follows:

SELECT HR_SAL.Payroll_No, PLAN_YEAR.Year, HR_SAL.Salary FROM HR_SAL, PLAN_YEAR WHERE (((IIf([Salary_Start_Date]>[Start_Date],"X",Null)) Is Null) AND ((IIf([Salary_End_Date]<[End_Date],"X",Null)) Is Null)) OR (((IIf([Salary_Start_Date]>[Start_Date],"X",Null))="X") AND ((IIf([Salary_Start_Date]<[End_Date],"X",Null))="X") AND ((IIf([Salary_End_Date]<[End_Date],"X",Null)) Is Null)) ORDER BY HR_SAL.Payroll_No, PLAN_YEAR.Year;

28439

I have had to add an additional field in the HR_SAL table referring to Salary_End_Date = 1 day less than the following Salary_Start_Date, if none ensuing then = 31/12/9999. Else you will have to write VBA code as one row of data is dependent on the next row of data - which is insufficient to solve by query alone. This additional field is self explanatory when hyou look at the table.

The HR Table is irrelevant in the query.

Hello Endre

Briliiant ! Thanks so much.

Regards
Narayan