Oops .. Have I answered my own question???
Code: Select all
SELECT tblStaff.Code, tblStaff.Surname, tblStaff.Initial, tblStaff.Workgroup, tblStaff.CurrencyRate, tblStaff.ProductivityRate, tblStaff.Active, tblStaff.Address, tblStaff.TelNo, tblStaff.OtherContact, tblStaff.EmployeeType, tblStaff.NINumber, tblStaff.Production, [productivityrate]*8/100 AS HrsAvail, tblStaff.Code AS UnitID, [Surname] & "," & [initial] AS Unit, [hrsAvail]*5 AS [Hrs Per Week], Weekdaysminusholidays(Date()-Weekday(Date(),0)+1,Date()-Weekday(Date())+7)*[productivityrate]/100 AS [Days This Week], Weekdaysminusholidays(Date()-Weekday(Date(),0)+1,Date()-Weekday(Date())+7)*[hrsavail] AS [Hrs This Week], Weekdaysminusholidays(DateSerial(Year(Date()),Month(Date()),1),(DateSerial(Year(Date()),Month(Date())+1,0)))*[productivityrate]/100 AS [Days This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Month(Date()),1),(DateSerial(Year(Date()),Month(Date())+1,0)))*[hrsavail] AS [Hrs This Month], Weekdaysminusholidays(DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,1),(DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+4,0)))*[productivityrate]/100 AS [Days This Qtr], Weekdaysminusholidays(DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+1,1),(DateSerial(Year(Date()),Int((Month(Date())-1)/3)*3+4,0)))*[hrsavail] AS [Hrs This Qtr], Weekdaysminusholidays(CDate("1/1/" & Year(Date())),CDate("1/1/" & Year(Date())+1)-1)*[productivityrate]/100 AS [Days This Year], Weekdaysminusholidays(CDate("1/1/" & Year(Date())),CDate("1/1/" & Year(Date())+1)-1)*[hrsavail] AS [Hrs This Year], tblStaff.Branch, tblStaff.IncludeAtt
FROM tblStaff
WHERE (((tblStaff.Workgroup) Not In ("SignsGraphics","Administration","Sales","Technologies")) AND ((tblStaff.Active)=True) AND ((tblStaff.Branch)=[Forms]![frmCallCentre]![Branch]));