Oleg_123 at xuppa.com
Oleg_123 at xuppa.com
Mon Feb 23 11:59:13 CST 2004
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