[AccessD] Union the transform queris ?

Jim Lawrence (AccessD) accessd at shaw.ca
Mon Mar 1 19:19:13 CST 2004


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"))
...




More information about the AccessD mailing list