[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 ?

> You included your aggregate field (Count([Ron Data Mid OP].ID)) twice in
> the query.  It should only appear in the TRANSFORM portion:
>
> TRANSFORM Count([Ron Data Mid OP].ID) AS Total
> SELECT "Investigation Opened" as [Status]
> FROM [Ron Data Mid OP]
> GROUP BY [Status]
> PIVOT Format([MS Date Opened],"mmm") In ("Sep","Oct","Nov","Dec");
>
> I'm assuming that you want a single row with overall totals by month,
> right?
>
>
> -----Original Message-----
> From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
> Sent: Monday, February 23, 2004 11:25 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] another Transform query question
>
>
> Why is it prompting me for status ?
>
> TRANSFORM Count([Ron Data Mid OP].ID) AS CountOfID
> SELECT "Investigation Opened" as [Status],  Count([Ron Data Mid OP].ID)
> AS Total
> FROM [Ron Data Mid OP]
> GROUP BY [Status]
> PIVOT Format([MS Date Opened],"mmm") In ("Sep","Oct","Nov","Dec");
>
> --------------------------------------------------------------------------------------------------------------------
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom
> they are addressed.
> If you have received this email in error please notify the
> originator of the message. This footer also confirms that this
> email message has been scanned for the presence of computer viruses.
>
> Any views expressed in this message are those of the individual
> sender, except where the sender specifies and with authority,
> states them to be the views of Tappe Construction Co.
>
> Scanning of this message and addition of this footer is performed
> by SurfControl E-mail Filter software in conjunction with
> virus detection software.
>
> _______________________________________________
> 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