show column totals in crosstab access

[Developer2U]

What I'm trying to do is display a row of column totals at the bottom of a crosstab query. I can easily get the row total. What I'm trying to show is the number of hours worked on a project per each employee.

       EmployeeA   EmployeeB
XYZ       15           17.0
XZX       20            5.5
Total     35           22.5

The current crosstab looks like this:

PARAMETERS [@Task_Order_Number] Text ( 20 ), [@BeginDate] DateTime, [@EndDate] DateTime;
TRANSFORM NZ(Sum(c.Hours),0) AS SumHours
SELECT e.ProjCode, Sum(c.Hours) AS [Total Hours]
FROM TaskOrder_tbl AS b INNER JOIN ((QA_Rep_tbl AS d INNER JOIN Task_tbl AS a ON d.UserID = a.UserID) INNER JOIN (Project_tbl AS e INNER JOIN Task_Details_tbl AS c ON e.ProjectID = c.ProjectID) ON a.TaskID = c.TaskID) ON b.TaskOrderID = c.TaskOrderID
WHERE b.TaskOrderID = [@Task_Order_Number] AND (a.TaskDate BETWEEN [@BeginDate] AND [@EndDate])
GROUP BY e.ProjCode
PIVOT d.User;

 

[dhookom]

You can't do this in a single Access crosstab. You can create totals in reports or forms. Your other option is to create another crosstab something like this:

PARAMETERS [@Task_Order_Number] Text ( 20 ), [@BeginDate] DateTime, [@EndDate] DateTime;
TRANSFORM NZ(Sum(c.Hours),0) AS SumHours
SELECT First("Total") as tProjCode, Sum(c.Hours) AS [Total Hours]
FROM TaskOrder_tbl AS b INNER JOIN ((QA_Rep_tbl AS d INNER JOIN Task_tbl AS a ON d.UserID = a.UserID) INNER JOIN (Project_tbl AS e INNER JOIN Task_Details_tbl AS c ON e.ProjectID = c.ProjectID) ON a.TaskID = c.TaskID) ON b.TaskOrderID = c.TaskOrderID
WHERE b.TaskOrderID = [@Task_Order_Number] AND (a.TaskDate BETWEEN [@BeginDate] AND [@EndDate])
PIVOT d.User;

Then you can UNION ALL the two queries together.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Developer2U]

The cross tab you suggested isn't working for me. I am thinking it is because it doesn't have a group by clause?

 

[Developer2U]

I did it like this and it works:

PARAMETERS [@Task_Order_Number] Text ( 255 ), [@BeginDate] DateTime, [@EndDate] DateTime;
TRANSFORM NZ(Sum(c.Hours),0) AS SumHours
SELECT  b.TaskOrderID
FROM TaskOrder_tbl AS b INNER JOIN ((QA_Rep_tbl AS d INNER JOIN Task_tbl AS a ON d.UserID=a.UserID) INNER JOIN (Project_tbl AS e INNER JOIN Task_Details_tbl AS c ON e.ProjectID=c.ProjectID) ON a.TaskID=c.TaskID) ON b.TaskOrderID=c.TaskOrderID
WHERE b.TaskOrderID=[@Task_Order_Number] AND (a.TaskDate BETWEEN [@BeginDate] AND [@EndDate])
GROUP By b.TaskOrderID
PIVOT d.User;

 

[Developer2U]

But your answer helped me to get to the solution I was looking for.

Thanks a million.