I have the following coding to report the 'Totals' for different TransCode.

strApptsSQL = "SELECT c.LName + ', ' + c.FName AS Examiner, " & _
"COUNT(Case TransCode WHEN 'R' THEN 1 ELSE NULL END) AS Requested, " & _
"COUNT(Case TransCode WHEN 'B' THEN 1 ELSE NULL END) AS Scheduled, " & _
.....
"FROM MyProgram.dbo.TransHistory AS T " & _
"LEFT JOIN MyWebServer.MyProgram.dbo.Requests AS r ON T.TransUserID = r.RequestStaffIDFK " & " " & _
"LEFT JOIN MyWebServer.MyProgram.dbo.Contacts AS c ON r.RequestStaffIDFK = c.ContactPK " & " " & _
"GROUP BY c.LName + ', ' + c.FName WITH ROLLUP " & _
"ORDER BY c.LName + ', ' + c.FName"


I created 2 requests for Name1 and 5 requests for Name2; then scheduled 2 requests and completed 1 for Name2.
Here is what the ReportTotals showed. Requests: 4=2x2, 25=5x5; Scheduled:10=2x5; and Completed:5=1x5. The first row showed '0'.
Why? What's wrong with the 'Rollup'?

           Request Scheduled Completed
            0 0 0
            29 10 5
Name1 4 0 0
Name2 25 10 5

__________________
C. Jeffrey Wang