Query to Capture Salary Changes

[fgbdrum]

My company prorates annual bonus payments by salary. Meaning if an employee’s salary changed at any point during the fiscal year, their annual bonus payment would be based on their prior salary, up until the point in time their salary changed, at which point the basis for their annual bonus calculation would be the new salary. Example:
Annual Bonus Plan participation period = 10/1/2023 – 9/30/2024

Salary on 10/1/2023; $75,000; Salary changed to $90,000 on 4/1/2024

Bonus 10/1/2023 – 3/31/2024 based on prior salary of $75,000


Bonus 4/1/2024 – 9/30/2024 based on new salary of $90,000

I would like to create an automated way in Access to audit for this, comparing the existing Annual Bonus table with the Salary Changes table (updated/uploaded periodically to capture new entries), which returns every salary change an employee has. Sample dB attached.

Using these examples, the query would return Jenny Kent as an employee who needs a prorated row, with a start date of 6/10/2024 and end date of 9/30/2024, since that is the date her salary changed from 75k to 92k, while at the same time returning a row showing that the time period 10/1/2023 – 6/9/2024 should be based on the prior salary of 75k. Query returns:

10/1/2023 – 6/9/2024 - $75,000
6/10/2024 – 9/30/2024 - $92,000

Additionally, the query would also return Phillip Smith as an employee who needs a prorated row with a start date of 5/27/2024 and end date of 9/30/2024, since that is the date his salary changed from 45k to 53k, while at the same time returning a row showing that the time period 10/1/2023 – 5/26/2024 should be based on the prior salary of 45k. Query returns:

10/1/2023 – 5/26/2024 - $45,000
5/27/2024 – 9/30/2024 - $53,000

Lastly, the query would ignore John Meyers and Rachel Rogers because their salary changes and commensurate proration has already been addressed and accounted for in a previous audit (and is therefore already reflected in the Annual Bonus table).
Thank you so much for any assistance in advance.

[pbaldy]

This isn't a direct answer to your question, but I would expect there to be another table of actual payroll amounts as they are paid out. A summary of that for the appropriate period would account for any salary changes made during the period and give you a base amount to apply a bonus percentage to.

[ano]

hope this is what u looking for

SELECT  a.[Person Number], a.Name, a.[Salary Change Date], a.[Annualized Salary], c.[Start Date], c.[End Date], c.[TIC Percent], c.[Annual Rate],(select b.[salary change date] from [salary changes] as b where b.[Person Number]=a.[Person Number] and b.[Salary Change Date]>a.[Salary Change Date]) as nw,datediff("d",a.[Salary Change Date],iif(not isnull(nw),nw,c.[end date])) as bonusdays,format(bonusdays*a.[Annualized Salary]*c.[TIC Percent]/36500,"$##,###.##") as calculatedFROM [Salary Changes] as a, [annual bonus] as c
WHERE a.[Person Number]=c.[employee id] and
a.[Annualized Salary] <> c.[Annual Rate] and a.[Salary Change Date] >= c.[Start Date] and a.[Salary Change Date] <= c.[End Date] ;

[ArviLaanemets]

hope this is what u looking for

Result row 2: salary was changed at 6/10/2024, but the change was for period from 10/1/2023 to 9/30/2024! And the salary was risen, but the annual rate remained same!?

And this person had 2 salaries for the period from 10/1/2023 to 9/30/2024!

[ano]

the text is not quite clear input and output text give less discussion i guess
u ask me abt row 2 , but u see i only used the delivered tables and there is no not

[ano]

would be much easier if u had output the result u expect;
text and database looks like not fitting/matching
looks like many double fields in ur database and or unneeded queries

[Welshgasman]

Crossposted https://www.utteraccess.com/topics/2066227?post=unread

[fgbdrum]

Hi there,

Thanks so much for taking a stab at this. Here's what I'm looking for the query to return:



Oh, and sorry about the cross-posting. Had no idea that was a thing that was frowned upon. Won't happen again.

[Welshgasman]

Only not informing people of the fact, is frowned upon, not the cross posting per se.

[ano]

Hi there,

Thanks so much for taking a stab at this. Here's what I'm looking for the query to return:



Oh, and sorry about the cross-posting. Had no idea that was a thing that was frowned upon. Won't happen again.

btw person number need number not text,need mandatory, need index

SELECT a.[Person Number], a.Name, a.[Salary Change Date] AS strt, iif( c.[Start Date]= a.[Salary Change Date], (select dateadd("d",-1,b.[salary change date]) from [salary changes] as b where b.[Person Number]=a.[Person Number] and b.[Salary Change Date]>a.[Salary Change Date]), c.[End Date] ) AS tll, a.[Annualized Salary]
FROM [Salary Changes] AS a, [annual bonus] AS c
WHERE a.[Person Number]=c.[employee id] and (select count(*) from [annual bonus] AS d where a.[Person Number]=d.[employee id])=1;

[ano]

btw with vba u can update 1st row and insert 2nd row , just by creating the output as in the sql to run it

[ano]

i think ur question is totally wrong, because u ask for query instead for update ;
the update should be part of the maintenance when changing salary info in the afterupdate vba routine like :

update [annual bonus] set [End Date]=#6/9/2024#
insert into [annual bonus]  ([Employee ID], [Employee Name], [Start Date], [End Date], [TIC Percent], [Annual Rate], [Adjustment Notes]) values (574089,'Kent,Jenny',#6/10/2024#,#9/30/2024#,0,92000,' ')