[AccessD] Union the transform queris ?

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
>