I have a query that calculates the actual salary for each month based on the start and end dates of employment. I've been asked to incorporate salary increases. So I thought I had figured out how I would do that. I have a table that gets imported that has personnel number, salary and date. Each time there is a new report from HR, I can import the new salary with the date. From that table I created a query that would reflect their current salary for each month of the year. The query is as follows:
SELECT [Job Activity].[HR Employee ID Code], Currentsalary.[Personnel Number],
IIf(([Currentsalary].[Date])>[txtstart] And ([Currentsalary].[Date])<DateAdd("m",1,[txtstart]),(Val([currentsalary].[annual salary])/12),([Job Activity]![Act Annual Salary]/12)) AS M1,
IIf(([Currentsalary].[Date]) Between DateAdd("m",1,[txtstart]) And DateAdd("m",2,[txtstart]),(Val([currentsalary].[annual salary])/12),[M1]) AS M2,
IIf((Currentsalary.Date) Between DateAdd("m",2,[txtstart]) And DateAdd("m",3,[txtstart]),(Val(currentsalary.[annual salary])/12),[M2]) AS M3,
IIf((Currentsalary.Date) Between DateAdd("m",3,[txtstart]) And DateAdd("m",4,[txtstart]),(Val(currentsalary.[annual salary])/12),[M3]) AS M4,
IIf((Currentsalary.Date) Between DateAdd("m",4,[txtstart]) And DateAdd("m",5,[txtstart]),(Val(currentsalary.[annual salary])/12),[M4]) AS M5,
IIf((Currentsalary.Date) Between DateAdd("m",5,[txtstart]) And DateAdd("m",6,[txtstart]),(Val(currentsalary.[annual salary])/12),[M5]) AS M6,
IIf((Currentsalary.Date) Between DateAdd("m",6,[txtstart]) And DateAdd("m",7,[txtstart]),(Val(currentsalary.[annual salary])/12),[M6]) AS M7,
IIf((Currentsalary.Date) Between DateAdd("m",7,[txtstart]) And DateAdd("m",8,[txtstart]),(Val(currentsalary.[annual salary])/12),[M7]) AS M8,
IIf((Currentsalary.Date) Between DateAdd("m",8,[txtstart]) And DateAdd("m",9,[txtstart]),(Val(currentsalary.[annual salary])/12),[M8]) AS M9,
IIf((Currentsalary.Date) Between DateAdd("m",9,[txtstart]) And DateAdd("m",10,[txtstart]),(Val(currentsalary.[annual salary])/12),[M9]) AS M10,
IIf((Currentsalary.Date) Between DateAdd("m",10,[txtstart]) And DateAdd("m",11,[txtstart]),(Val(currentsalary.[annual salary])/12),[M10]) AS M11,
IIf((Currentsalary.Date) Between DateAdd("m",11,[txtstart]) And DateAdd("m",12,[txtstart]),(Val(currentsalary.[annual salary])/12),[M11]) AS M12
FROM [Job Activity] LEFT JOIN Currentsalary ON [Job Activity].[HR Employee ID Code] = Currentsalary.[Personnel Number];
Summarizing, The first month (M1) if it doesn't find an update, it uses the existing salary. The second month it uses the update salary figure or previous months figure depending if it exists etc.
This works partially. I have two update records (that shows a salary change) for each employee right now and the query results show two result records for each employee. What I want is one result per employee reflecting both salary updates across the 12 months. Is that possible?
Thanks!!!
SELECT [Job Activity].[HR Employee ID Code], Currentsalary.[Personnel Number],
IIf(([Currentsalary].[Date])>[txtstart] And ([Currentsalary].[Date])<DateAdd("m",1,[txtstart]),(Val([currentsalary].[annual salary])/12),([Job Activity]![Act Annual Salary]/12)) AS M1,
IIf(([Currentsalary].[Date]) Between DateAdd("m",1,[txtstart]) And DateAdd("m",2,[txtstart]),(Val([currentsalary].[annual salary])/12),[M1]) AS M2,
IIf((Currentsalary.Date) Between DateAdd("m",2,[txtstart]) And DateAdd("m",3,[txtstart]),(Val(currentsalary.[annual salary])/12),[M2]) AS M3,
IIf((Currentsalary.Date) Between DateAdd("m",3,[txtstart]) And DateAdd("m",4,[txtstart]),(Val(currentsalary.[annual salary])/12),[M3]) AS M4,
IIf((Currentsalary.Date) Between DateAdd("m",4,[txtstart]) And DateAdd("m",5,[txtstart]),(Val(currentsalary.[annual salary])/12),[M4]) AS M5,
IIf((Currentsalary.Date) Between DateAdd("m",5,[txtstart]) And DateAdd("m",6,[txtstart]),(Val(currentsalary.[annual salary])/12),[M5]) AS M6,
IIf((Currentsalary.Date) Between DateAdd("m",6,[txtstart]) And DateAdd("m",7,[txtstart]),(Val(currentsalary.[annual salary])/12),[M6]) AS M7,
IIf((Currentsalary.Date) Between DateAdd("m",7,[txtstart]) And DateAdd("m",8,[txtstart]),(Val(currentsalary.[annual salary])/12),[M7]) AS M8,
IIf((Currentsalary.Date) Between DateAdd("m",8,[txtstart]) And DateAdd("m",9,[txtstart]),(Val(currentsalary.[annual salary])/12),[M8]) AS M9,
IIf((Currentsalary.Date) Between DateAdd("m",9,[txtstart]) And DateAdd("m",10,[txtstart]),(Val(currentsalary.[annual salary])/12),[M9]) AS M10,
IIf((Currentsalary.Date) Between DateAdd("m",10,[txtstart]) And DateAdd("m",11,[txtstart]),(Val(currentsalary.[annual salary])/12),[M10]) AS M11,
IIf((Currentsalary.Date) Between DateAdd("m",11,[txtstart]) And DateAdd("m",12,[txtstart]),(Val(currentsalary.[annual salary])/12),[M11]) AS M12
FROM [Job Activity] LEFT JOIN Currentsalary ON [Job Activity].[HR Employee ID Code] = Currentsalary.[Personnel Number];
Summarizing, The first month (M1) if it doesn't find an update, it uses the existing salary. The second month it uses the update salary figure or previous months figure depending if it exists etc.
This works partially. I have two update records (that shows a salary change) for each employee right now and the query results show two result records for each employee. What I want is one result per employee reflecting both salary updates across the 12 months. Is that possible?
Thanks!!!