[AccessD] another Transform query question

Oleg,

I don't think you really need the IIF.  The where statement using just the
dates should be enough and might speed up the query.



> TRANSFORM Count([Ron Data Mid OP].[ID]) AS Total
> SELECT "Investigation Opened" AS Status
> FROM [Ron Data Mid OP]

> WHERE ([Ron Data Mid OP].[MS Date Opened] >= [Forms]![Report
> Form]![Start_Date]) And ([Ron Data Mid OP].[MS Date 
> Opened]<=[Forms]![Report Form]![End_Date])
> 
> 
> GROUP BY "Investigation Opened"
> 
> 
> PIVOT Format([AMTS Date Opened],"mmm") In
> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","
> Nov","Dec");
> 

Patti

> -----Original Message-----
> From: O'Connor, Patricia [mailto:Patricia.O'Connor at dfa.state.ny.us]
> Sent: Monday, February 23, 2004 01:51 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] another Transform query question
> 
> 
> 
> Try 
> 
> TRANSFORM Count([Ron Data Mid OP].ID) AS Total
> SELECT "Investigation Opened" AS Status
> FROM [Ron Data Mid OP]
> WHERE (((IIf([Ron Data Mid OP]![MS Date Opened]>=[Forms]![Report
> Form]![Start_Date] And [Ron Data Mid OP]![MS Date 
> Opened]<=[Forms]![Report
> Form]![End_Date],"Cases Closed")) Is Not Null))
> 
> 
> GROUP BY "Investigation Opened"
> 
> 
> PIVOT Format([AMTS Date Opened],"mmm") In
> ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","
> Nov","Dec");
> 
> 
> Patti
> 
> > -----Original Message-----
> > From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
> > Sent: Monday, February 23, 2004 12:59 PM
> > To: accessd at databaseadvisors.com
> > Subject: RE: [AccessD] another Transform query question
> > 
> > 
> > I would hope it would be that simple.. nee, it keeps 
> prompting me for
> > status :((
> > (not to mention that it takes like 4 minutes for a query to open)
> > Also, they change their mind, they now want it to prompt, 
> > like I had in
> > the first one..
> > 
> > TRANSFORM Count([Ron Data Mid OP].ID) AS Total
> > SELECT "Investigation Opened" AS Status
> > FROM [Ron Data Mid OP]
> > WHERE (((IIf([Ron Data Mid OP]![MS Date Opened]>=[Forms]![Report
> > Form]![Start_Date] And [Ron Data Mid OP]![MS Date 
> > Opened]<=[Forms]![Report
> > Form]![End_Date],"Cases Closed")) Is Not Null))
> > GROUP BY [Status]
> > PIVOT Format([AMTS Date Opened],"mmm") In
> > ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","
> > Nov","Dec");
> > 
> > 
> > whats the better way to do prompts ?
> > 
> 
> 
> > 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>