Group by two criteria (1 Viewer)

sleibo

New member
Local time
Today, 03:40
Joined
Dec 16, 2018
Messages
5
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!
 

SHANEMAC51

Active member
Local time
Today, 10:40
Joined
Jan 28, 2022
Messages
310
Например, первый запрос tblTemp показывает следующие итоги

id_temp Sum Of Minutes Employee Name Week
1900Pit9
22400Pit10
32500Pit11
42600sleibo9
52600sleibo10
62700sleibo11
7500murphy9
83000murphy10
91200scott9
102400scott10
112500scott11

Затем запрос tblTemp по сотруднику

Code:
ВЫБЕРИТЕ сумму(IIf(tblTemp.[ SumOfMinutes]<=2400,tblTemp. [SumOfMinutes],2400)) AS Regular Minutes,
Sum(IIf(tblTemp.[ SumOfMinutes]<=2400,0,[SumOfMinutes]-2400)) AS Overtime Minutes,
tblTemp.Имя_сотрудника
ОТ tblTemp
ГРУППА ПО tblTemp.Имя_сотрудника

Показывает итоговые показатели за период по сотрудникам
Regular MinutesOvertime MinutesEmployee Name
2900600murphy
5700100pit
6000100scott
7200700sleibo
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:40
Joined
Feb 19, 2002
Messages
42,981
Just FYI, using the names of functions or properties as column names will cause problems with code. I've included a sample with the most egregious problems.
 

Attachments

  • UsefulCode_20220130.zip
    170.6 KB · Views: 308

sleibo

New member
Local time
Today, 03:40
Joined
Dec 16, 2018
Messages
5
Thank you, Shanemac51! I was not aware that you could use IIF statement as Access criteria. With a little modification, this gave me just what I needed.
 

June7

AWF VIP
Local time
Yesterday, 23:40
Joined
Mar 9, 2014
Messages
5,424
Did you view suggestion provided in your other thread?
 

sleibo

New member
Local time
Today, 03:40
Joined
Dec 16, 2018
Messages
5
Yes, thank you too, June7! I also didn't realize that access-programmers.co.uk cross posts to accessforums.net!
 

Users who are viewing this thread

Top Bottom