<!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.3718.0" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Great</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Learn something on this list every day</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Will try this in the morning and let you know how
it goes</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Martin</FONT></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=adtp@touchtelindia.net
href="mailto:adtp@touchtelindia.net">A.D.Tejpal</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> Thursday, October 09, 2003 11:51
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] CrossTab </DIV>
<DIV><FONT face=Arial size=2></FONT><BR></DIV>
<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>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BODY></HTML>