[AccessD] Union the transform queris ?

Patricia/Jim - thanks a lot :--)

> oleg/Jim
>
> Leave the UnION ALL in but take out the second union before the SELECT
> "Pre Investigation Opened" AS Status and it will work.
>
> Thanks Jim - this is good to know will go into my code archive
>
> Patti
>
>
>
>> -----Original Message-----
>> From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca]
>> Sent: Monday, March 01, 2004 08:19 PM
>> To: Access Developers discussion and problem solving
>> Subject: RE: [AccessD] Union the transform queris ?
>>
>>
>> Hi Oleg:
>>
>> With just a quick look the code below should work. This
>> assumes that the
>> data brought back from each TRANSFORM section is identical in
>> field count
>> and type. Just needs closed curved brackets around each section and
>> appropriate Union type clause in between.
>>
>> HTH
>> Jim
>>
>> ...
>> (TRANSFORM Count([Ron Data spr].[ID]) AS Total
>> SELECT "Closed without Investigation" AS Status
>> FROM [Ron Data spr]
>> WHERE ([Ron Data spr].[Preliminary Investigation Closed] >=
>> [Forms]![Report Form]![Start_Date]) And ([Ron Data spr].[Preliminary
>> Investigation Closed]<=[Forms]![Report Form]![End_Date]) and
>> ([Preliminary
>> Investigation closed] is not null and [Investigation Opened] is null)
>> GROUP BY  "Closed without Investigation"
>> PIVOT Format([Preliminary Investigation Closed],"mmm") In
>> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","
>> Nov","Dec"))
>> UNION ALL
>> (TRANSFORM Count([Ron Data spr].[ID]) AS Total
>> Union SELECT "Pre Investigation Opened" AS Status
>> FROM [Ron Data spr]
>> WHERE ([Ron Data spr].[Preliminary Investigation Opened] >=
>> [Forms]![Report Form]![Start_Date]) And ([Ron Data spr].[Preliminary
>> Investigation Opened]<=[Forms]![Report Form]![End_Date]) and
>> [Preliminary
>> Investigation Opened] is not null
>> GROUP BY "Pre Investigation Opened"
>> PIVOT Format([Preliminary Investigation Opened],"mmm") In
>> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","
>> Nov","Dec"))
>> ...
>>
>> --
>> _______________________________________________
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
> --
> _______________________________________________
> 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