Crosstab Query Help

[spudracer]

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:

	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:

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;

 

[theDBguy]

Hi. Try adding one more criteria.

 WHERE (((tblMusterStatus.Use)=True)) AND Dept Is Not Null

Hope that helps...

 

[spudracer]

Hi. Try adding one more criteria.

 WHERE (((tblMusterStatus.Use)=True)) AND Dept Is Not Null

Hope that helps...

That takes care of the Null row, but it drops the columns that are also null.

 

[theDBguy]

That takes care of the Null row, but it drops the columns that are also null.

Hmm, not sure I follow. Your columns are based on Status, so why would filtering out null Dept would affect it? Can you post a sample db with test data?

 

[arnelgp]

alternative to Create another Query from your Crosstab
with Criteria: Dept Not Is Null

 

[spudracer]

alternative to Create another Query from your Crosstab
with Criteria: Dept Not Is Null

That did it, thank you!