I'm trying to create a crosstab query that relies on dynamic column headings (user can change which columns are visible).
How I want it to look:
How it's showing up:
While I do want all of the columns to show regardless if they have any value other than 0 in them, I don't want the blank row.
I have the crosstab setup like this:
How I want it to look:
Present | |
Admin | 1 |
Operations | 2 |
How it's showing up:
Leave | Present | |
0 | ||
Admin | 1 | |
Operations | 2 |
While I do want all of the columns to show regardless if they have any value other than 0 in them, I don't want the blank row.
I have the crosstab setup like this:
Code:
TRANSFORM Count(qryPersActive.MusterStatus) AS CountOfMusterStatus
SELECT qryPersActive.Dept
FROM tblMusterStatus LEFT JOIN qryPersActive ON tblMusterStatus.Status = qryPersActive.MusterStatus
WHERE (((tblMusterStatus.Use)=True))
GROUP BY qryPersActive.Dept
PIVOT tblMusterStatus.Status;