Hi,
I am looking for some advice on whether to continue using a query or change to a query which uses a function to deal with the hard parts of the calculation.
I am creating a rota/shift management system. The database is fully normalised and is now working to the extent that shifts can be entered, changed and a rota for each week can be viewed.
I am now working on a query to calculate the premium payments that each worker should receive for working evening, night and weekend shifts.
Each worker gets paid a basic rate and then on top of that a premium (as percentage of basic rate) as follows:
Monday to Friday
00:00-06:00 33%
06:00-09:00 25%
17:00-23:00 25%
23:00-00:00 33%
Saturday
All day 50%
Sunday
All day 100%
I only need to calculate and show the premiums, I only use the basic rate as part of the calculation.
Shifts start at all hours/half hours. Workers do not get paid for breaks and they get 0.5 hours break if they work 6 hours or more and total of 1 hour break once they have worked 10 hours.
So, I have written and tested a query which works for shifts which begin on Saturdays and end on Saturdays or Sundays. It works fine but the number of nested iif’s required just for the easiest calculation makes me think that queries on their own are not the best way to proceed. (This really stretched me working it out and even now, looking at the query, I can only understand parts of it at a time)
I am wondering about passing a function the essential elements of start time, end time, hourly rate and day of week shift begins, calculating the premium in the function which then returns the premium.
Can anyone recommend whether I should bite the bullet and tackle the even more complex queries required for Monday through Friday or use the somewhat easier to understand (for me at least) capabilities of VBA Functions to deal with the calculations required? Thank you!
I am asking out of a belief that queries run faster than VBA code but unsure whether the development time required to write the queries (I’m estimating another day to two days) makes it worth using them rather than a function which I can do in half a day.
I will paste the query below. It references:
Tbl_Employees (which stores non confidential employee details)
Tbl_Rota_Shifts (which stores the shifts details)
Tbl_DP_Details (which stores confidential employee details like salary and contracted hours and is linked on a one to one relationship with Tbl_Employees)
SELECT tbl_Employees.EmployeeID, tbl_Rota_Shifts.Start_Date, tbl_DP_Details.Salary, tbl_DP_Details.Contracted_Hours, Format(([salary]/52)/[contracted_hours],"Currency") AS HourlyRate, DatePart("w",[start_date],2) AS DayOfWeekAsNumber, tbl_Rota_Shifts.Start_Time, tbl_Rota_Shifts.End_Time, CDate(IIf([end_time]>[start_time],[end_time]-[start_time],(#12/30/1899 23:30:0#-[start_time])+#12/30/1899 0:30:0#)) AS Sat_Hours, CDate(IIf([end_time]<[start_time],[end_time]-#12/30/1899#,0)) AS Post_Mid_Sat_Hours, CDbl(DatePart("h",[Sat_Hours])+(DatePart("n",[Sat_Hours])/60)) AS Pre_Mid_Hours_Sng, CDbl(DatePart("h",[Post_Mid_Sat_Hours])+(DatePart("n",[Post_Mid_Sat_Hours])/60)) AS Post_Mid_Hours_Sng, Format(IIf([pre_mid_hours_sng]+[post_Mid_hours_sng]<6,[pre_mid_hours_sng]*([hourlyrate]*0.5)+([post_mid_hours_sng]*[hourlyrate]),IIf([pre_mid_hours_sng]+[post_mid_hours_sng]<10,IIf([post_mid_hours_sng]>0.5,[pre_mid_hours_sng]*([hourlyrate]*0.5)+([post_mid_hours_sng]-0.5)*[hourlyrate],([pre_mid_hours_sng]-0.5)*([hourlyrate]*0.5)),IIf([post_mid_hours_sng]>1,[pre_mid_hours_sng]*([hourlyrate]*0.5)+([post_mid_hours_sng]-1)*[hourlyrate],IIf([post_mid_hours_sng]=1,[pre_mid_hours_sng]*([hourlyrate]*0.5),IIf([post_mid_hours_sng]=0.5,([pre_mid_hours_sng]-0.5)*([hourlyrate]*0.5),([pre_mid_hours_sng]-1)*([hourlyrate]*0.5)))))),"Currency") AS PPCalc
FROM tbl_Rota_Shifts INNER JOIN (tbl_DP_Details INNER JOIN tbl_Employees ON tbl_DP_Details.FK_EmployeeID = tbl_Employees.EmployeeID) ON tbl_Rota_Shifts.FK_EmployeeID = tbl_DP_Details.FK_EmployeeID
WHERE (((tbl_Rota_Shifts.Start_Date) Between [forms]![frm_Premia_Payments]![txtFirstDay] And [forms]![frm_Premia_Payments]![txtLastDay]) AND ((DatePart("w",[start_date],2)) Like 6) AND ((tbl_Rota_Shifts.FK_Duty_Type) Like 1) AND ((tbl_Rota_Shifts.Shift_Changed) Like False));
I am looking for some advice on whether to continue using a query or change to a query which uses a function to deal with the hard parts of the calculation.
I am creating a rota/shift management system. The database is fully normalised and is now working to the extent that shifts can be entered, changed and a rota for each week can be viewed.
I am now working on a query to calculate the premium payments that each worker should receive for working evening, night and weekend shifts.
Each worker gets paid a basic rate and then on top of that a premium (as percentage of basic rate) as follows:
Monday to Friday
00:00-06:00 33%
06:00-09:00 25%
17:00-23:00 25%
23:00-00:00 33%
Saturday
All day 50%
Sunday
All day 100%
I only need to calculate and show the premiums, I only use the basic rate as part of the calculation.
Shifts start at all hours/half hours. Workers do not get paid for breaks and they get 0.5 hours break if they work 6 hours or more and total of 1 hour break once they have worked 10 hours.
So, I have written and tested a query which works for shifts which begin on Saturdays and end on Saturdays or Sundays. It works fine but the number of nested iif’s required just for the easiest calculation makes me think that queries on their own are not the best way to proceed. (This really stretched me working it out and even now, looking at the query, I can only understand parts of it at a time)
I am wondering about passing a function the essential elements of start time, end time, hourly rate and day of week shift begins, calculating the premium in the function which then returns the premium.
Can anyone recommend whether I should bite the bullet and tackle the even more complex queries required for Monday through Friday or use the somewhat easier to understand (for me at least) capabilities of VBA Functions to deal with the calculations required? Thank you!
I am asking out of a belief that queries run faster than VBA code but unsure whether the development time required to write the queries (I’m estimating another day to two days) makes it worth using them rather than a function which I can do in half a day.
I will paste the query below. It references:
Tbl_Employees (which stores non confidential employee details)
Tbl_Rota_Shifts (which stores the shifts details)
Tbl_DP_Details (which stores confidential employee details like salary and contracted hours and is linked on a one to one relationship with Tbl_Employees)
SELECT tbl_Employees.EmployeeID, tbl_Rota_Shifts.Start_Date, tbl_DP_Details.Salary, tbl_DP_Details.Contracted_Hours, Format(([salary]/52)/[contracted_hours],"Currency") AS HourlyRate, DatePart("w",[start_date],2) AS DayOfWeekAsNumber, tbl_Rota_Shifts.Start_Time, tbl_Rota_Shifts.End_Time, CDate(IIf([end_time]>[start_time],[end_time]-[start_time],(#12/30/1899 23:30:0#-[start_time])+#12/30/1899 0:30:0#)) AS Sat_Hours, CDate(IIf([end_time]<[start_time],[end_time]-#12/30/1899#,0)) AS Post_Mid_Sat_Hours, CDbl(DatePart("h",[Sat_Hours])+(DatePart("n",[Sat_Hours])/60)) AS Pre_Mid_Hours_Sng, CDbl(DatePart("h",[Post_Mid_Sat_Hours])+(DatePart("n",[Post_Mid_Sat_Hours])/60)) AS Post_Mid_Hours_Sng, Format(IIf([pre_mid_hours_sng]+[post_Mid_hours_sng]<6,[pre_mid_hours_sng]*([hourlyrate]*0.5)+([post_mid_hours_sng]*[hourlyrate]),IIf([pre_mid_hours_sng]+[post_mid_hours_sng]<10,IIf([post_mid_hours_sng]>0.5,[pre_mid_hours_sng]*([hourlyrate]*0.5)+([post_mid_hours_sng]-0.5)*[hourlyrate],([pre_mid_hours_sng]-0.5)*([hourlyrate]*0.5)),IIf([post_mid_hours_sng]>1,[pre_mid_hours_sng]*([hourlyrate]*0.5)+([post_mid_hours_sng]-1)*[hourlyrate],IIf([post_mid_hours_sng]=1,[pre_mid_hours_sng]*([hourlyrate]*0.5),IIf([post_mid_hours_sng]=0.5,([pre_mid_hours_sng]-0.5)*([hourlyrate]*0.5),([pre_mid_hours_sng]-1)*([hourlyrate]*0.5)))))),"Currency") AS PPCalc
FROM tbl_Rota_Shifts INNER JOIN (tbl_DP_Details INNER JOIN tbl_Employees ON tbl_DP_Details.FK_EmployeeID = tbl_Employees.EmployeeID) ON tbl_Rota_Shifts.FK_EmployeeID = tbl_DP_Details.FK_EmployeeID
WHERE (((tbl_Rota_Shifts.Start_Date) Between [forms]![frm_Premia_Payments]![txtFirstDay] And [forms]![frm_Premia_Payments]![txtLastDay]) AND ((DatePart("w",[start_date],2)) Like 6) AND ((tbl_Rota_Shifts.FK_Duty_Type) Like 1) AND ((tbl_Rota_Shifts.Shift_Changed) Like False));
