Summary Column in CrossTab uery

[hhzyousafi]

I need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:

PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime;
TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode
SELECT tblAttendance.EmployeeName
FROM tblAttendance
WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime]))
GROUP BY tblAttendance.EmployeeName
PIVOT Format([AttendanceDate],"Short Date");​

This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.

 

[hhzyousafi]

I am guessing with 43 views and no responses I have either asked an extremely simple question or an incredibly complicated one.

 

[CJ_London]

It is a simple solution

1. Create another column in your crosstab query with the field

DaysPresent: iif(AttendanceCode="P",1,0)

2. change the total row from groupby to sum
3. set the crosstab to row heading

 

[hhzyousafi]

Thank you very much CJ_London!