I have a query I am using and determining which "call_shift" a call came in on.
The problem is the way I have it working right now, I can only get the shifts to be on the hours, I would like to have one break on the half hour.
(i.e. 8 - 12:30, 12:30-5:00, rather than 8-1 and 1-5)
My last attempt (which you see below) I tried adding in a field to calculate the Mins (Min_in) as well as the Hours (which I had before), but that seemed to move me in the wrong direction, as I got an error "You tried to execute a query that does not include the specified expression 'ACALLID' as part of an aggregate function."
Here is the code I was using that had it working as long as the shifts changed on the hour:
Any suggestions on how I can tell this to break on the half hours?
Thank you
The problem is the way I have it working right now, I can only get the shifts to be on the hours, I would like to have one break on the half hour.
(i.e. 8 - 12:30, 12:30-5:00, rather than 8-1 and 1-5)
My last attempt (which you see below) I tried adding in a field to calculate the Mins (Min_in) as well as the Hours (which I had before), but that seemed to move me in the wrong direction, as I got an error "You tried to execute a query that does not include the specified expression 'ACALLID' as part of an aggregate function."
Code:
SELECT SWB_A_CALL.ACALLID, SWB_SW_CUSTOMER.AMNEMONIC, SWB_A_CALL.APRIORITY, SWB_A_CALL.ACALLTYPE, Team_Type.Team_Type, Employee_Team.ALASTCOMMAFIRSTNAME, Employee_Team.TEAM, [SWB_SW_PROD_RELEASE Query].SWNAME, Hour([ADATECREATED]) AS Hour_in, Min([ADATECREATED]) AS Min_in, IIf([hour_in]>7 And ([hour_in]<12 And [Min_in]<30),1,IIf(([hour_in]>12 And [Min_in]>=30) And [hour_in]<17,2,IIf([hour_in]>17 And [hour_in]<20,3,4))) AS Call_Shift, SWB_A_CALL.ADATECREATED, SWB_A_CALL.ARESPONDBY, SWB_A_CALL.ADATECLOSED, nhw([ADATECREATED],[ADATECLOSED]," 8:00","20:00") AS [Response Time], SWB_A_CALL.APROBDESC, SWB_A_CALL.ACREATEDBY, SWB_A_CALL.ACASEID
FROM (((SWB_A_CALL LEFT JOIN [SWB_SW_PROD_RELEASE Query] ON SWB_A_CALL.AINSTPRODID = [SWB_SW_PROD_RELEASE Query].SWINSTPRODID) INNER JOIN SWB_SW_CUSTOMER ON SWB_A_CALL.ACUSTOMERID = SWB_SW_CUSTOMER.SWCUSTOMERID) LEFT JOIN Team_Type ON SWB_A_CALL.ACALLTYPE = Team_Type.Type) LEFT JOIN Employee_Team ON SWB_A_CALL.ACLOSEDBY = Employee_Team.SWPERSONID
WHERE (((SWB_A_CALL.ADATECLOSED) Between Date()-1 And Date()));
Here is the code I was using that had it working as long as the shifts changed on the hour:
Code:
SELECT SWB_A_CALL.ACALLID, SWB_SW_CUSTOMER.AMNEMONIC, SWB_A_CALL.APRIORITY, SWB_A_CALL.ACALLTYPE, Team_Type.Team_Type, Employee_Team.ALASTCOMMAFIRSTNAME, Employee_Team.TEAM, [SWB_SW_PROD_RELEASE Query].SWNAME, Hour([ADATECREATED]) AS Hour_in, IIf([hour_in]>7 And [hour_in]<13,1,IIf([hour_in]>12 And [hour_in]<17,2,IIf([hour_in]>16 And [hour_in]<20,3,4))) AS Call_Shift, SWB_A_CALL.ADATECREATED, SWB_A_CALL.ARESPONDBY, SWB_A_CALL.ADATECLOSED, nhw([ADATECREATED],[ADATECLOSED]," 8:00","20:00") AS [Response Time], SWB_A_CALL.APROBDESC, SWB_A_CALL.ACREATEDBY, SWB_A_CALL.ACASEID
FROM (((SWB_A_CALL LEFT JOIN [SWB_SW_PROD_RELEASE Query] ON SWB_A_CALL.AINSTPRODID = [SWB_SW_PROD_RELEASE Query].SWINSTPRODID) INNER JOIN SWB_SW_CUSTOMER ON SWB_A_CALL.ACUSTOMERID = SWB_SW_CUSTOMER.SWCUSTOMERID) LEFT JOIN Team_Type ON SWB_A_CALL.ACALLTYPE = Team_Type.Type) LEFT JOIN Employee_Team ON SWB_A_CALL.ACLOSEDBY = Employee_Team.SWPERSONID
WHERE (((SWB_A_CALL.ADATECLOSED) Between Date()-1 And Date()));
Any suggestions on how I can tell this to break on the half hours?
Thank you