[AccessD] Transform query question

Oleg,
This is the default functionality of cross-tab queries (i.e. supress blank
columns).  It is overridden by specifying an IN clause in the PIVOT
statement, to force the specfied columns to show regardless of whether or
not they contain data.

Omit the In ("Jan","Feb"... from the query and it should return what you
want, e.g.:

TRANSFORM Count([Ron Data enf].ID) AS CountOfID
SELECT [Ron Data enf].REFERDEPTorSRO AS Origin, Count([Ron Data enf].ID)
AS Total
FROM [Ron Data enf]
WHERE (((IIf([Ron Data enf]![Date Case Ref Received]>=[Forms]![Report
Form]![Start_Date] And [Ron Data enf]![Date Case Ref
Received]<=[Forms]![Report Form]![End_Date],"Cases Closed")) Is Not Null))
GROUP BY [Ron Data enf].REFERDEPTorSRO
PIVOT Format([AMTS Date Opened],"mmm");

-----Original Message-----
From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
Sent: Monday, February 23, 2004 9:18 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Transform query question


Good Morning Group

I have a transform query, and  I would like for it to show only the
columns that actually contain data, so if there is no data for September
and October, just show August column  followed by November.  Is it
possible ?

Thank yo
Oleg

TRANSFORM Count([Ron Data enf].ID) AS CountOfID
SELECT [Ron Data enf].REFERDEPTorSRO AS Origin, Count([Ron Data enf].ID)
AS Total
FROM [Ron Data enf]
WHERE (((IIf([Ron Data enf]![Date Case Ref Received]>=[Forms]![Report
Form]![Start_Date] And [Ron Data enf]![Date Case Ref
Received]<=[Forms]![Report Form]![End_Date],"Cases Closed")) Is Not Null))
GROUP BY [Ron Data enf].REFERDEPTorSRO
PIVOT Format([AMTS Date Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");



-----------------------------------------
Get Breaking News from CNN, ABC, NBC, CBS Now.
http://www.xuppa.com/news/?link=webmail


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--------------------------------------------------------------------------------------------------------------------
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this email in error please notify the
originator of the message. This footer also confirms that this
email message has been scanned for the presence of computer viruses.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

Scanning of this message and addition of this footer is performed
by SurfControl E-mail Filter software in conjunction with 
virus detection software.