Code:
SELECT DISTINCT Table_All_Data.Date, Max(Table_All_Data.Hour) AS MaxOfHour, Table_All_Data.HMTTYP, Max(Table_All_Data.HMPQTY) AS MaxOfHMPQTY, Table_All_Data.HMBLDG, Table_All_Data.USDESC, Sum(IIf([HOUR]=0,[HMQTY])) AS Hour0, Sum(IIf([HOUR]=1,[HMQTY])) AS Hour1, Sum(IIf([HOUR]=2,[HMQTY])) AS Hour2, Sum(IIf([HOUR]=3,[HMQTY])) AS Hour3, Sum(IIf([HOUR]=4,[HMQTY])) AS Hour4, Sum(IIf([HOUR]=5,[HMQTY])) AS Hour5, Sum(IIf([HOUR]=6,[HMQTY])) AS Hour6, Sum(IIf([HOUR]=7,[HMQTY])) AS Hour7, Sum(IIf([HOUR]=8,[HMQTY])) AS Hour8, Sum(IIf([HOUR]=9,[HMQTY])) AS Hour9, Sum(IIf([HOUR]=10,[HMQTY])) AS Hour10, Sum(IIf([HOUR]=11,[HMQTY])) AS Hour11, Sum(IIf([HOUR]=12,[HMQTY])) AS Hour12, Sum(IIf([HOUR]=13,[HMQTY])) AS Hour13, Sum(IIf([HOUR]=14,[HMQTY])) AS Hour14, Sum(IIf([HOUR]=15,[HMQTY])) AS Hour15, Sum(IIf([HOUR]=16,[HMQTY])) AS Hour16, Sum(IIf([HOUR]=17,[HMQTY])) AS Hour17, Sum(IIf([HOUR]=18,[HMQTY])) AS Hour18, Sum(IIf([HOUR]=19,[HMQTY])) AS Hour19, Sum(IIf([HOUR]=20,[HMQTY])) AS Hour20, Sum(IIf([HOUR]=21,[HMQTY])) AS Hour21, Sum(IIf([HOUR]=22,[HMQTY])) AS Hour22, Sum(IIf([HOUR]=23,[HMQTY])) AS Hour23, Format([DATE],"m/d/yy") AS Formated_Date, Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([USDESC],"WAREHOUSE SU",""),"WAREHOUSE T",""),"WAREHOUSE",""),"WAREHOUS",""),"WAREHOU",""),"WAREHO",""),"SUPERVISOR",""),"BLD 2",""),"-","")," ","")," ","")," ","") & " (" & LTrim([HMBLDG]) & ")" AS Formatted_User
FROM Table_All_Data
WHERE (((Table_All_Data.USDESC)=Forms![Productivity Search]!FromForm_Associate) And ((Table_All_Data.HMTTYP)=Forms![Productivity Search]!FromForm_Activity) And ((Table_All_Data.HMBLDG)=Forms![Productivity Search]!FromForm_Bldg)) Or (((Table_All_Data.USDESC)=Forms![Productivity Search]!FromForm_Associate) And ((Forms![Productivity Search]!FromForm_Activity) Is Null)) Or (((Table_All_Data.HMTTYP)=Forms![Productivity Search]!FromForm_Activity) And ((Forms![Productivity Search]!FromForm_Associate) Is Null)) Or (((Forms![Productivity Search]!FromForm_Activity) Is Null) And ((Forms![Productivity Search]!FromForm_Associate) Is Null))
GROUP BY Table_All_Data.Date, Table_All_Data.HMTTYP, Table_All_Data.HMBLDG, Table_All_Data.USDESC, Format([DATE],"m/d/yy")
HAVING (((Table_All_Data.Date) Between [Forms]![Productivity Search]![FromForm_StartDate] And [Forms]![Productivity Search]![FromForm_EndDate]))
ORDER BY Table_All_Data.USDESC, Table_All_Data.DATE;
Any guidance, advice, or suggestions would be greatly appreciated. I've searched, but unfortunately my particular request isn't a popular one.