[AccessD] Transform query question

:))
thanks, it worked, only it doesn't sort the records right, did
alphabetically I guess- Feb, Jan, March..
can I sort it in normal way ?

> Took a look at the MSKB and found the following article:
>
> ACC2000: Error When Running Crosstab Query with a Parameter
> http://support.microsoft.com/default.aspx?scid=kb;en-us;209778&Product=acc20
> 00
>
> Jet is confusing your form control parameters with column names.  In the
> Query designer, select Query > Parameters, and enter the name of the
> controls (i.e. [Forms]![Report Form]![Start_Date]) as parameters with
> the appropriate data type, and all will be well.
>
> So there.  We both learned something today...
>
>
> -----Original Message-----
> From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
> Sent: Monday, February 23, 2004 9:48 AM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Transform query question
>
>
> I get an error --
> Microsoft Jet Database Engine does not recognise '[Forms]![Report
> Form]![Start_Date]' as a valid field name of expression
> It works fine when the lest line is present :--\
>
>
>> 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.
>>
>> _______________________________________________
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>
>
>
> -----------------------------------------
> 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.
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



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