<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2726.2500" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><STRONG>Martin,</STRONG></DIV>
<DIV> </DIV>
<DIV> While making a crosstab query, automatic creation of a
column with heading <> signifies that it contains a mixture of
nameless columns corresponding to Null values in the field designated to
provide the column headings.</DIV>
<DIV> </DIV>
<DIV> As a simple illustration, let table T_Test have three
fields with values as follows -</DIV>
<DIV> F_1 (Text)
F_2(Text) F_3(Number)</DIV>
<DIV> A
L
3</DIV>
<DIV>
A 4</DIV>
<DIV> B 6</DIV>
<DIV> B R
2</DIV>
<DIV> C
L 5</DIV>
<DIV>
C
R
7</DIV>
<DIV> </DIV>
<DIV> If a crosstab query is created in a routine manner,
F_1(group by) as row, F_2(group by) as column and F_3(Sum) as value, it will
look like -</DIV>
<DIV> F_1
<>
L
R</DIV>
<DIV>
A 4
3
</DIV>
<DIV> B
6
2</DIV>
<DIV> C
5
7</DIV>
<DIV> </DIV>
<DIV> Normally, this can be left as it is - while taking
care to interpret the column headed <> as follows -</DIV>
<DIV> Item A has 4 numbers where field F_2 has Null
value.</DIV>
<DIV> Item B has 6 numbers where field F_2 has Null
value.</DIV>
<DIV> </DIV>
<DIV> However, if due to some reason, you are very keen to
have more descriptive headings (in lieu of <>) without interfering
with basic data, calculated field F_2M can be used instead of field F_2
-</DIV>
<DIV> F_2M: IIf(Len([F_2])>0, [F_2], [F_1] &
"_NULL")</DIV>
<DIV> </DIV>
<DIV> The crosstab query will now give the following display
-</DIV>
<DIV> F_1
A_NULL
B_NULL L
R
<DIV>
A 4 3
</DIV>
<DIV> B
6 2</DIV>
<DIV> C
5 7</DIV>
<DIV> </DIV>
<DIV> It will be nice if you could kindly try it out and let
me know.</DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV><STRONG>A.D.Tejpal</STRONG></DIV>
<DIV><STRONG>--------------</STRONG></DIV></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=MPorter@acsalaska.com href="mailto:MPorter@acsalaska.com">Porter,
Mark</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">'Access Developers discussion and
problem solving'</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, October 08, 2003
23:38</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] CrossTab </DIV>
<DIV><BR></DIV>You can also investigate the 'Switch' command, switching a
<NULL> value for<BR>an actual text string like "Null".<BR><BR>>
-----Original Message-----<BR>> From: Charlotte Foust
[mailto:cfoust@infostatsystems.com]<BR>> Sent: Wednesday, October 08, 2003
7:56 AM<BR>> To: Access Developers discussion and problem solving<BR>>
Subject: RE: [AccessD] CrossTab <BR>> <BR>> <BR>> Is the crosstab
using the values as the column headings? If so, you<BR>> could use a
calculated field to return an IsNull expression.<BR>> <BR>> Charlotte
Foust<BR>> <BR>> -----Original Message-----<BR>> From: Martin Reid
[mailto:mwp.reid@qub.ac.uk] <BR>> Sent: Wednesday, October 08, 2003 7:47
AM<BR>> To: Access Developers discussion and problem solving<BR>>
Subject: Re: [AccessD] CrossTab <BR>> <BR>> <BR>> I need to keep the
nulls as they are valid responses.<BR>> <BR>> Martin<BR>> <BR>>
<BR>> ----- Original Message ----- <BR>> From: "Susan Harkins" <<A
href="mailto:ssharkins@bellsouth.net">ssharkins@bellsouth.net</A>><BR>>
To: "Access Developers discussion and problem solving"<BR>> <<A
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>><BR>>
Sent: Wednesday, October 08, 2003 4:38 PM<BR>> Subject: Re: [AccessD]
CrossTab<BR>> <BR>> <BR>> > Can't you handle the Null in the
underlying query?<BR>> ><BR>> > Susan H.<BR>> ><BR>>
><BR>> > > Running a major cross tab report<BR>> >
><BR>> > > A lot of the values are null<BR>> > ><BR>>
> > Problem<BR>> > ><BR>> > > The Crosstab creats a
new column called <> (I dont create this)<BR>> > ><BR>> >
> when it comes across a null row it assigns it a value of 1<BR>> >
><BR>> > > how can I get it to stop creating the 1 value as it is
<BR>> tossing out <BR>> > > my calculations.<BR>> >
><BR>> > ><BR>> > ><BR>> > >
Martin<BR></BLOCKQUOTE></BODY></HTML>