I am not certain if this should be posted in SQL or in VBA programming ....
I am not good at SQL but work with the Design Grid and SQL view.
Employees may work at multiple addresses per day.
TblData fields = Minutes, Week, EmployeeName, Date, Address
Q_ConcatenateFName&LName has fields = EmployeeName
Week = the week to which each date belongs
Each row has minutes worked on a job on each date, by employee
This query successfully determines the number of minutes worked between StartDate and EndDate:
SELECT Sum(TblData.Minutes) AS SumOfMinutes, [Q_ConcatenateFName&Lname].EmployeeName, TblData.Week
FROM [Q_ConcatenateFName&Lname] INNER JOIN TblData ON [Q_ConcatenateFName&Lname].EmployeeName = TblData.EmployeeName
WHERE (((TblData.Address)<>"PTO" And (TblData.Address)<>"Holiday") AND ((TblData.Date)>=[Forms]![F_Payroll Reports]![StartDate] And (TblData.Date)<= [Forms]![F_Payroll Reports]![EndDate]))
GROUP BY [Q_ConcatenateFName&Lname].EmployeeName, TblData.Week;
However what I want is:
For each week: If SumOfMinutes <=2400, RegularMinutes = SumOfMinutes;
If SumOfMinutes > 2400, RegularMinutes = 2400 AND OvertimeMinutes =
SumOfMinutes - 2400
For all dates between StartDate and EndDate:
TotalRegularMinutes = Sum(RegularMinutes), AND
TotalOvertimeMinutes = Sum(OvertimeMinutes),
grouped by EmployeeName
Can you help?
Thank you in advance!
I am not good at SQL but work with the Design Grid and SQL view.
Employees may work at multiple addresses per day.
TblData fields = Minutes, Week, EmployeeName, Date, Address
Q_ConcatenateFName&LName has fields = EmployeeName
Week = the week to which each date belongs
Each row has minutes worked on a job on each date, by employee
This query successfully determines the number of minutes worked between StartDate and EndDate:
SELECT Sum(TblData.Minutes) AS SumOfMinutes, [Q_ConcatenateFName&Lname].EmployeeName, TblData.Week
FROM [Q_ConcatenateFName&Lname] INNER JOIN TblData ON [Q_ConcatenateFName&Lname].EmployeeName = TblData.EmployeeName
WHERE (((TblData.Address)<>"PTO" And (TblData.Address)<>"Holiday") AND ((TblData.Date)>=[Forms]![F_Payroll Reports]![StartDate] And (TblData.Date)<= [Forms]![F_Payroll Reports]![EndDate]))
GROUP BY [Q_ConcatenateFName&Lname].EmployeeName, TblData.Week;
However what I want is:
For each week: If SumOfMinutes <=2400, RegularMinutes = SumOfMinutes;
If SumOfMinutes > 2400, RegularMinutes = 2400 AND OvertimeMinutes =
SumOfMinutes - 2400
For all dates between StartDate and EndDate:
TotalRegularMinutes = Sum(RegularMinutes), AND
TotalOvertimeMinutes = Sum(OvertimeMinutes),
grouped by EmployeeName
Can you help?
Thank you in advance!