Crosstab: <> values not showing

[Lars Brownies]

I have a crosstab query in which null values result in a column called <>.
To make sure my report can be run, I've used the IN statement
(IN("<>","M","F")) to make sure the 3 fields are always there, even when
there is no null value.

Strange thing is that the M and F values are shown correctly but the <>
values don't show at all. The total value of all 3 fields does include the
null values of the <> field.

Any ideas what I'm doing wrong?

Thanks,
Lars

 

[Douglas J. Steele]

I think it would depend on how the <> column is being generated.

What's the rest of your SQL statement?

 

[Lars Brownies]

The full query:

TRANSFORM Count(ID_person) AS CountOfID_person
SELECT Region, Count(ID_person) AS [Total ID_person]
FROM tblPerson
GROUP BY Region
PIVOT Gender IN("<>","Female","Male");

which results in the following testresults

qryRegionGender Region Total ID_person <> Female Male
1 1

1
2 3
1 1
3 1
1



There is one person with region 2 and without a gender value, but it's not
shown in the table (total is 3 but you only see 1 for female and 1 for
male). If I remove IN("<>","Female","Male") it works, but when there is no
missing value the <> field isn't there and I can't open my report.

Lars

 

[Lars Brownies]

That should have been the following testresults:

Region TotalID_person <> Female Male
1 1 1
2 3 1 1
3 1 1


Lars

The full query:

TRANSFORM Count(ID_person) AS CountOfID_person
SELECT Region, Count(ID_person) AS [Total ID_person]
FROM tblPerson
GROUP BY Region
PIVOT Gender IN("<>","Female","Male");

which results in the following testresults

qryRegionGender Region Total ID_person <> Female Male
1 1

1
2 3
1 1
3 1
1



There is one person with region 2 and without a gender value, but it's not
shown in the table (total is 3 but you only see 1 for female and 1 for
male). If I remove IN("<>","Female","Male") it works, but when there is no
missing value the <> field isn't there and I can't open my report.

Lars

I think it would depend on how the <> column is being generated.

What's the rest of your SQL statement?

 

[Duane Hookom]

It looks like you are using a field like [Gender] as the column heading and
the field might contain nulls. If so, I would change the column heading field
to something like:
Nz([Gender],"N/A")
Then set the Column Heading property to:
"F","M","N/A"

 

[Lars Brownies]

Thanks, it works!.... but only with "NA" and not with "N/A". I think Access
doesn't like special signs like >< / etc.
Lars

It looks like you are using a field like [Gender] as the column heading
and
the field might contain nulls. If so, I would change the column heading
field
to something like:
Nz([Gender],"N/A")
Then set the Column Heading property to:
"F","M","N/A"

--
Duane Hookom
Microsoft Access MVP

I think it would depend on how the <> column is being generated.

What's the rest of your SQL statement?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)





.

 

[John Spencer]

This works for me as expected (Access 2003). Note that I am not using "<>",
but am using Null in the in clause. When I use null, the column headinf of
the result does show "<>"

TRANSFORM Count(Nominations.ID) AS CountOfID1
SELECT Nominations.ID, Count(Nominations.ID) AS CountOfID
FROM Nominations
GROUP BY Nominations.ID
PIVOT Nominations.NomineeName In ("Ted",Null);

And this works also
TRANSFORM Count(Nominations.ID) AS CountOfID1
SELECT Nominations.ID, Count(Nominations.ID) AS CountOfID
FROM Nominations
GROUP BY Nominations.ID
PIVOT NZ(Nominations.NomineeName, "N/A") In ("Ted","N/A");

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

That should have been the following testresults:

Region TotalID_person <> Female Male
1 1 1
2 3 1 1
3 1 1


Lars

The full query:

TRANSFORM Count(ID_person) AS CountOfID_person
SELECT Region, Count(ID_person) AS [Total ID_person]
FROM tblPerson
GROUP BY Region
PIVOT Gender IN("<>","Female","Male");

which results in the following testresults

qryRegionGender Region Total ID_person <> Female Male
1 1

1
2 3
1 1
3 1
1



There is one person with region 2 and without a gender value, but it's not
shown in the table (total is 3 but you only see 1 for female and 1 for
male). If I remove IN("<>","Female","Male") it works, but when there is no
missing value the <> field isn't there and I can't open my report.

Lars

I think it would depend on how the <> column is being generated.

What's the rest of your SQL statement?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a crosstab query in which null values result in a column called
<>. To make sure my report can be run, I've used the IN statement
(IN("<>","M","F")) to make sure the 3 fields are always there, even when
there is no null value.

Strange thing is that the M and F values are shown correctly but the <>
values don't show at all. The total value of all 3 fields does include
the null values of the <> field.

Any ideas what I'm doing wrong?

Thanks,
Lars

 

[Lars Brownies]

Thanks John.
Indeed both work. I must have been doing something wrong with the second
option, since that didn't work when I first tried it. I prefer this option
since you get a meaningfull field name in stead of <>.

Lars

This works for me as expected (Access 2003). Note that I am not using
"<>", but am using Null in the in clause. When I use null, the column
headinf of the result does show "<>"

TRANSFORM Count(Nominations.ID) AS CountOfID1
SELECT Nominations.ID, Count(Nominations.ID) AS CountOfID
FROM Nominations
GROUP BY Nominations.ID
PIVOT Nominations.NomineeName In ("Ted",Null);

And this works also
TRANSFORM Count(Nominations.ID) AS CountOfID1
SELECT Nominations.ID, Count(Nominations.ID) AS CountOfID
FROM Nominations
GROUP BY Nominations.ID
PIVOT NZ(Nominations.NomineeName, "N/A") In ("Ted","N/A");

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

That should have been the following testresults:

Region TotalID_person <> Female Male
1 1 1
2 3 1 1
3 1 1


Lars

The full query:

TRANSFORM Count(ID_person) AS CountOfID_person
SELECT Region, Count(ID_person) AS [Total ID_person]
FROM tblPerson
GROUP BY Region
PIVOT Gender IN("<>","Female","Male");

which results in the following testresults

qryRegionGender Region Total ID_person <> Female Male
1 1

1
2 3
1 1
3 1
1



There is one person with region 2 and without a gender value, but it's
not shown in the table (total is 3 but you only see 1 for female and 1
for male). If I remove IN("<>","Female","Male") it works, but when there
is no missing value the <> field isn't there and I can't open my report.

Lars


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> schreef in
bericht I think it would depend on how the <> column is being generated.

What's the rest of your SQL statement?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a crosstab query in which null values result in a column called
<>. To make sure my report can be run, I've used the IN statement
(IN("<>","M","F")) to make sure the 3 fields are always there, even
when there is no null value.

Strange thing is that the M and F values are shown correctly but the
<> values don't show at all. The total value of all 3 fields does
include the null values of the <> field.

Any ideas what I'm doing wrong?

Thanks,
Lars