Calculate Percent of Total from Crosstab Query 1

[ThomasLafferty]

Hi there. I have a crosstab query for reporting survey results by department. Currently it returns the number of times individuals in a given department chose a certain category. This works fine. In my report which references this query, I need to know how many individuals as a total percentage of all individuals chose the given option.

Here's the SQL:

TRANSFORM Count(tblNonEssentialTasks.PercentTime) AS CountOfPercentTime
SELECT tblResults.Department
FROM tblResults INNER JOIN tblNonEssentialTasks ON tblResults.EmpNo = tblNonEssentialTasks.EmpNo
GROUP BY tblResults.Department
PIVOT tblNonEssentialTasks.PercentTime;

Here's a screenshot of the design grid:

Here's a screenshot of the results:

Here's the report design:

And preview of the report:

What I am looking for is a way of knowing, for example, out of the 20 warehouseman, how many indicated that they wasted 25 percent of their time (this is 25 column) as a percentage of the total number of employees in that department.

Thanks in advance!
Tom

Born once die twice; born twice die once.

 

[dhookom]

You need a count of the total number of employees in each dept.

TRANSFORM Count(tblNonEssentialTasks.PercentTime) AS CountOfPercentTime
SELECT tblResults.Department, Count(tblNonEssentialTasks.EmpNo) as DeptCount
FROM tblResults INNER JOIN tblNonEssentialTasks ON tblResults.EmpNo = tblNonEssentialTasks.EmpNo
GROUP BY tblResults.Department
PIVOT tblNonEssentialTasks.PercentTime;

You should then be able to divide the counts by the DeptCount.

You should probably also set the column headings property to 0,25,50,74,100

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]

 

[ThomasLafferty]

Thanks a million! As usual, you were right on the money.

Born once die twice; born twice die once.