Double Queries/Double Tables/SQL Catch 22 1

[Shlomo]

I need to produce a table which searches for the sum of the products ordered from all orders.&nbsp;&nbsp;So a 'sum as' and&nbsp;&nbsp;'group by' handles that very nicely, except that part of the output needs to be a description of the product.&nbsp;&nbsp;So what you say, well the description is a memo data type and&nbsp;&nbsp;Access does not allow memo data types to be included in an aggregate function.&nbsp;&nbsp;<br>SO don't include it, well in order for an aggregate function to function all of the members of the select statement must be included in the group by clause.<br><br>CF does not allow for nested cfoutputs that are assigned to cfqueries and if they are not assigned to the query in the header&nbsp;&nbsp;they inly produce one row of output.<br><br>Double tables - one for each cfquery/output has proven to be a nightmare to try and line up.<br><br>An additional probelem caused by the 'memo' data type is the fact that you cannot use the&nbsp;&nbsp;Distinct keyword on it and if I dont' I'll get 1 description from each time a prduct appears on an order and I just want one description to match with&nbsp;&nbsp;the product count.<br>

 

[DarkMan]

Somewhere in the Allaire website (<A HREF="

http://www.allaire.com/"

TARGET="_new">

http://www.allaire.com/</A>)

there is a custom tag called &lt;CF_MERGEQUERY&gt; that should help, either that or try doing the math in CF....

 

[SnakeHollywood]

Try using &lt;CFOUPUT groupby=&quot;column&quot;&gt;<br>inside your first CFOUTPUT where you have repeated columns. This will only show the distinct records for the specified column. <p>Russ Michaels<br><a href=mailto:russ@satachi.com>russ@satachi.com</a><br><a href=

http://www.satachi.com>Satachi

Internet Development</a><br>Rapid Web application development, specialising in Coldfusion, database integration and e-commerce solutions.