[AccessD] Union the transform queris ?

Pretty sure you will have to make 2 queries for this when using ACCESS
TABLES 

I created a dummy table and tried to do what you wanted in one and it was
not working. SO I removed the transform and made just a plain select query
which I used as input to the crosstab

Somebody else may know of another way for ACCESS TABLES 

query 1 named qrytst1 in my test:

SELECT  tst1.ID,
"Closed without Investigation" AS Status,
PrelimClosedt as InvDt
FROM TST1
WHERE  (PrelimClosedt >= [Start_Date]) 
And (PrelimClosedt <= [End_Date])
and  (TST1.PrelimClosedt  Is Not Null  AND  TST1.InvestigationOpen  Is Null)

UNION  all
SELECT tst1.Id,
 "Pre Investigation Opened" AS Status,
PrelimOpendt as InvDt
FROM TST1
WHERE  (PrelimOpendt >= [Start_Date]) 
And (PrelimOpendt <= [End_Date])
and  (TST1.PrelimOpendt  Is Not Null  AND  TST1.InvestigationOpen  Is Null) 

query two named qrytst1_Crosstab in my test

TRANSFORM Count([ID])
SELECT [Status], Count([ID]) AS [Total Of ID]
FROM qrytst1
GROUP BY [Status]
PIVOT Format([InvDt],"mmm") IN
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


HTH
Patti
> -----Original Message-----
> From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
> Sent: Monday, March 01, 2004 11:20 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Union the transform queris ?
> 
> 
> hey group.
> 
> Is it possible to perform an union query on 2 (or more) 
> transform queries
> if the field names are he same ?
> 
> 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")
> 
> 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");
> 
> 
> 
> -----------------------------------------
> 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
>