Group by more than one criteria?

[sleibo]

I'm not sure if this should be posted in Progamming, or SQL, or VBA ....

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!

[June7]

Cross post https://www.access-programmers.co.uk...iteria.321610/

Not clear to me what you want. Group by employee to list weeks under each employe and have a conditional calculation for each week's minutes?

[sleibo]

I want a sum of RegularMinutes and sum of OvertimeMinutes worked between StartDate and EndDate, for each employee. For each week, the first 2400 minutes are regular time and the additional minutes are overtime. For instance, if StartDate = 1/2/2022, EndDate = 1/19/2022, and Employee1 worked 2600 minutes in week 1, 2800 minutes in week 2 and 1500 minutes in week 3, then TotalRegularMinutes would be 2400 + 2400 + 1500, and TotalOvertimeMinutes would be (2600-2400)+(2800-2400)+1500. This calculation should be done for each employee.

[June7]

Calculation for the overtime in Detail section for each week record:

=IIf([SumOfMinutes] > 2400, [SumOfMinutes] - 2400, 0)

Calculation for sum of overtime in group section header or footer:

=Sum(IIf([SumOfMinutes] > 2400, [SumOfMinutes] - 2400, 0))