Well, normally to get out of this conundrum, I'll just fiddle around with the SQL. SQL won't allow grouping by text or bit datatypes unfortunately - Andrew's point - so you need a CF solution.
I have a couple ideas on that, but let me give you a SQL option first. Use the CAST() function in SQL to change the text or bit datatype to something that can be grouped. SELECT agency_no, fund_no, providingAgency_no, providingFund_no, count(*) as totlCount, CAST(Submitted as int) AS Submitted from sco567_568 where agency_no = '#client.agency_no#' and Fiscal_Year = #client.cfy# group by agency_No, fund_No, providingAgency_No, ProvidingFUnd_No, CAST(Submitted as int) order by fund_no, providingAgency_no, providingFund_No Let me know if this works. I think it should. Michael B. Dorr eLab Web Application Developer Owen @ Vanderbilt University [EMAIL PROTECTED] -----Original Message----- From: Andrew Peterson [mailto:[EMAIL PROTECTED]] Sent: Friday, June 14, 2002 8:53 AM To: CF-Talk Subject: SQL Group By Query Question Hi, This one is difficult to explain but I'll give it a shot anyway. I have a result set produced from the following query: SELECT agency_no, fund_no, providingAgency_no, providingFund_no, count(*) as totlCount from sco567_568 where agency_no = '#client.agency_no#' and Fiscal_Year = #client.cfy# group by agency_No, fund_No, providingAgency_No, ProvidingFUnd_No order by fund_no, providingAgency_no, providingFund_No The result set looks something like this (I am grouping the output by fund): Fund: 1 *Providing Agency* *Providing Fund* *totlCount* 444 ||| 1 ||| (13 Entries) 444 ||| 347 ||| (1 Entry) 444 ||| 921 ||| (1 Entry) Fund: 100 *Providing Agency* *Providing Fund* *totlCount* 444 ||| 1 ||| (7 Entries) 492 ||| 1 ||| (1 Entry) There is also yes/no field in this table called Submitted which indicates whether the record has been submitted. I cannot check for it in the query above because it would ruin my grouping and totals. I need to determine if all of the entries in each row above have a value of yes in this field. If they do, that row is completed and I need to indicate that to the user. For example, if all 13 entries in the first row above have a Yes in the Submitted field, I would place a check mark or some other indicator next to it. Otherwise, it will not. Is there any way to do this? Thanks in advance for any assistance. Andrew ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
