CrossTab Query

[Tom Hart]

I would like to show all rows (Division) in a crosstab
query whether the fields I am counting are null or not. I
know how to force the column headings, but is there a way
to force all the rows to be listed in a crosstab?

Here is the current code.

TRANSFORM Count(qryEmployeeCases.EmpID) AS CountOfEmpID
SELECT qryEmployeeCases.Division, Count
(qryEmployeeCases.EmpID) AS [Total Of EmpID], Sum
(qryEmployeeCases.CountOfCaseKey) AS SumOfCountOfCaseKey
FROM qryEmployeeCases, tblDates
WHERE (((qryEmployeeCases.DateEntered) Between [BegDate]
And [EndDate]))
GROUP BY qryEmployeeCases.Division
PIVOT qryEmployeeCases.EmpInd In ("Current","Corrective
Action","Terminated","Other");

Thank-you in advance.

 

[Duane Hookom]

Take the results of this query and create a new query that includes the
query and your table of all divisions. Set the join to include all records
from your table of all divisions.

 

[Ragnar Midtskogen]

You can join the crosstab query in a left join with the Division table,
joining them on the division, and then select the division from the division
table instead of the qryEmployeeCases query. Left join means show all the
rows from the division table and only those from the qryEmployeeCases query
where the division is the same. It is not clear whether the
qryEmployeeCases.Division field contain a division ID or the name, or maybe
division number.