Updating current salary based on personnel number

[Delindan]

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!!!

 

[dhookom]

Please provide your table structure, some sample records, and desired output. I think you are working way too hard on this.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

The primary table [job activity] contains position information:
start date
end date
act annual salary
employee number
name
cost center
...many others

I currently have a query that given a particular start date [txtstart] will give you their salary cost for the time span. I'm going to be given regular salary updates and would like the query to reflect past salary levels as well as updates. What I have done (and will continue if I get this working) is to put the salary updates into a table called [Current Salary]:

employee number
annual salary
date

I would like to create a query which given a particular start date, will give a positions salary level for each month that has passed. For example, I have Jon Deere employee number 100 who on 9/1/2011 was making 50,000. On January 1 he had a 10% raise so 55,000 and then in March was given another 5,000 raise. His record in [Job Activity] would be as follows:

start date enddate act annual salary employee number name
1/1/1982 null 50,000 100 Jon Deere

He would have 2 records in [Current Salary]:

Employee Number annual salary date
100 55,000 1/1/2012
100 60,000 3/1/2012

Annual salary query with a txtstart date of 9/1/2011 I would like to look like this:

M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
50,000 50,000 50,000 50,000 55,000 55,000 60,000 60,000 60,000 60,000 60,000 60,000

Basically I would like it to use the [job activity].[act annual salary] for the first month and then for subsequent months, would use the previous months figure unless there was an update.
Let me know if I left anything out! Thanks

 

[dhookom]

I'm not sure what you are going to do if the salary changes mid month. Why is there a salary in the Job Activity table and the Current Salary table?

I would first create a query with a subquery based on the [Current Salary] table that adds an end date (or the current date) as a column. Then I would create a table that had every month. You then combine these where the month in the month table is between the start and end dates with the salary.

Sorry to say I am going off the grid for about 5 days so if you have additional questions, I trust someone else can help.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

The salary changes were an addition after we had been using what I had which is why there is an annual salary in the [job activity] table. We are not being day to day accurate, so if something happens within a month it would represent the month. So if a salary change happened on 3/12 it would be representative of March for example. I'm not quite sure how to do what you're suggesting. Is this possibly a union query? Any hints on how to start on that?

Thanks!

 

[dhookom]

Sorry but I have phone access only so I trust someone else might help. It doesn't help that you didn't suggest if you had tried anything I suggested.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Well this isn't much to look at and it doesn't work. I'm also not sure it will give me what I need. However one step at a time because I'm still trying to figure out union queries. Here is what I came up with:

Select [HR Employee ID Code], [Act Annual Salary] From [Job Activity]
Union
Select [Personnel Number], [Annual Salary]
From [currentsalary]
WHERE (Date Between [txtstart] And dateadd("m",1,[txtstart]);

If I got that to work I could do one for each month right?

 

[Delindan]

I did a bunch more reading on union queries and it really wasn't what I needed however the plus side of that is now I know a bit more about that. You were right in that I was making this a whole lot more difficult than it needed to be. I just summarized on HR Employee Number and from there got their monthly salary adjustments. duh! I'm my own worse enemy. Thanks for the input