<cfoutput query="active_suppliers" group="SURVEY_DUE_DT">
<tr>
<td colspan="3">
<font size="+2" color="000080">Surveys Due In:</font>
<font size="+2" color="0000ff"><strong>#DateFormat(active_suppliers.SURVEY_DUE_DT, "MMM")#</strong></font>
</td>
</tr>
<cfoutput>
<cfquery name="query2" datasource="some_source">
SELECT DISTINCT VENDOR.ADDR.CITY_NM, VENDOR.ADDR.STATE_CD, VENDOR.ADDR.CNTRY_CD, VENDOR.ADDR.ZIP_CD,
As far as the rest of the code goes, I haven't had time to read through it yet, but maybe this can jumpstart you.
At 01:55 PM 12/3/2001 -0600, you wrote:
Does no one have any ideas regarding the following problem?
As requested, the code below provides the output at the bottom of this conversation topic. However, I have not figured out how to do a recordcount at the group level and how to display those results on the same lines as their respective group headers. If anyone has any ideas about how to do this, I would appreciate it.
<cfquery name="query1" datasource="some_source">
SELECT VENDOR.VNDR_ADDR.ADDR_TYP_CD, VENDOR.VNDR_ADDR.BSNS_UNIT_ID, MAX(VENDOR.VNDR_ADDR.EFF_DT) AS EFF_DT,
VENDOR.VNDR_ADDR.VNDR_ID, SPA.VNDR_SURVEY.SURVEY_DUE_DT
FROM VENDOR.VNDR_ADDR, VENDOR.VNDR, VENDOR.ADDR, SPA.VNDR_SURVEY
WHERE VENDOR.VNDR_ADDR.BSNS_UNIT_ID = 'FTW'
AND VENDOR.VNDR_ADDR.ADDR_TYP_CD = 'M'
AND VENDOR.VNDR_ADDR.ADDR_ID = VENDOR.ADDR.ADDR_ID
AND VENDOR.VNDR_ADDR.BSNS_UNIT_ID = VENDOR.VNDR.BSNS_UNIT_ID
AND VENDOR.VNDR_ADDR.BSNS_UNIT_ID = SPA.VNDR_SURVEY.BSNS_UNIT_ID
AND VENDOR.VNDR_ADDR.VNDR_ID = VENDOR.VNDR.VNDR_ID
AND VENDOR.VNDR_ADDR.VNDR_ID = SPA.VNDR_SURVEY.VNDR_ID
AND SPA.VNDR_SURVEY.ACTUAL_SURVEY_DT IS NULL
AND SUBSTR(SPA.VNDR_SURVEY.SURVEY_DUE_DT, 8, 2) = '#Right(year, 2)#'
AND VENDOR.VNDR.LREP_EMP_ID = '#form.login_id#'
GROUP BY VENDOR.VNDR_ADDR.ADDR_TYP_CD, VENDOR.VNDR_ADDR.BSNS_UNIT_ID, VENDOR.VNDR_ADDR.VNDR_ID,
SPA.VNDR_SURVEY.SURVEY_DUE_DT
ORDER BY SPA.VNDR_SURVEY.SURVEY_DUE_DT, VENDOR.VNDR_ADDR.VNDR_ID
</cfquery>
<cfif #query1.recordcount# LTE 0>
<h2 align="center">There are no open surveys assigned to the selected individual for the specified year.</h2>
<cfabort>
</cfif>
<table cellpadding="5" border="1">
<tr>
<th>Supplier Code</th>
<th>Supplier Name and Location</th>
<th>Survey Type</th>
</tr>
<cfoutput query="active_suppliers" group="SURVEY_DUE_DT">
<tr>
<td colspan="3">
<font size="+2" color="000080">Surveys Due In:</font>
<font size="+2" color="0000ff"><strong>#DateFormat(active_suppliers.SURVEY_DUE_DT, "MMM")#</strong></font>
</td>
</tr>
<cfoutput>
<cfquery name="query2" datasource="some_source">
SELECT DISTINCT VENDOR.ADDR.CITY_NM, VENDOR.ADDR.STATE_CD, VENDOR.ADDR.CNTRY_CD, VENDOR.ADDR.ZIP_CD,
VENDOR.VNDR.VNDR_ID, VENDOR.VNDR.LN1_FULL_NM, SPA.VNDR_SURVEY.SURVEY_DUE_DT,
FLDHELP.T_FIELD_VALUES.VALUE_DESC
FROM VENDOR.ADDR, VENDOR.VNDR, VENDOR.VNDR_ADDR, SPA.VNDR_SURVEY, FLDHELP.T_FIELD_VALUES
WHERE VENDOR.VNDR_ADDR.ADDR_TYP_CD LIKE '#query1.ADDR_TYP_CD#%'
AND VENDOR.VNDR_ADDR.BSNS_UNIT_ID LIKE '#query1.BSNS_UNIT_ID#%'
AND VENDOR.VNDR_ADDR.EFF_DT = '#query1.EFF_DT#'
AND VENDOR.VNDR_ADDR.VNDR_ID = '#query1.VNDR_ID#'
AND SPA.VNDR_SURVEY.SURVEY_DUE_DT = '#query1.SURVEY_DUE_DT#'
AND VENDOR.VNDR_ADDR.ADDR_ID = VENDOR.ADDR.ADDR_ID
AND VENDOR.VNDR_ADDR.BSNS_UNIT_ID = VENDOR.VNDR.BSNS_UNIT_ID
AND VENDOR.VNDR_ADDR.BSNS_UNIT_ID = SPA.VNDR_SURVEY.BSNS_UNIT_ID
AND VENDOR.VNDR_ADDR.VNDR_ID = SPA.VNDR_SURVEY.VNDR_ID
AND VENDOR.VNDR_ADDR.VNDR_ID = VENDOR.VNDR.VNDR_ID
AND SPA.VNDR_SURVEY.ACTUAL_SURVEY_DT IS NULL
AND SPA.VNDR_SURVEY.SURVEY_TYPE = FLDHELP.T_FIELD_VALUES.FIELD_VALUE
AND FLDHELP.T_FIELD_VALUES.FIELD_NAME = 'SURVEY_TYPE'
AND FLDHELP.T_FIELD_VALUES.SYSTEM_NAME = 'SPA'
ORDER BY SPA.VNDR_SURVEY.SURVEY_DUE_DT
</cfquery>
<cfif Trim(query2.LN1_FULL_NM) IS NOT "">
<tr>
<td align="center"><a href=""../supplier_session.cfm?cid=#query2.VNDR_ID#"" target="_blank">#Right(query2.VNDR_ID, 6)#</a></td>
<td>
#query2.LN1_FULL_NM#<br>
#Trim(query2.CITY_NM)#,
#Trim(query2.STATE_CD)#,
#Trim(query2.CNTRY_CD)#
#Trim(query2.ZIP_CD)#
</td>
<td>#query2.VALUE_DESC#</td>
</tr>
</cfif>
</cfoutput>
</cfoutput>
<tr>
<td colspan="3" align="right"><strong><u>Total Open Surveys:</u></strong> <cfoutput>#query2.recordcount#</cfoutput></td>
</tr>
</table>
============================================
Gary L. Alford
Supplier Product Assurance
Bell Helicopter Textron
Phone: (817) 280-6233
Fax: (817) 278-6233
mailto:[EMAIL PROTECTED]
============================================
- -----Original Message-----
- From: Karen Harker [mailto:[EMAIL PROTECTED]]
- Sent: Monday, December 03, 2001 1:29 PM
- To: [EMAIL PROTECTED]
- Subject: Re: Nested Grouped CFOutput and Grouped RecordCount
- Please summarize for list, because this is a function that would be very useful to many people.
- Thanks.
- Karen R. Harker, MLS
- UT Southwestern Medical Library
- 5323 Harry Hines Blvd.
- Dallas, TX 75390-9049
- 214-648-1698
- http://www.swmed.edu/library/
- >>> [EMAIL PROTECTED] 12/3/01 10:03:58 AM >>>
- I am looping through a set of SQL queries (Oracle Tables) that is providing
- an output using CF 4.5.2. I have established a set of "group=" attributes
- in my cfoutput that provides a good visual of the data. However, now I
- would like to include a recordcount of each group and output that count with
- the group headers and output the total report as follows:
- Jan Surveys Due: 2
- _______________________________________
- Survey 1
- Survey 2
- _______________________________________
- Feb Surveys Due: 3
- _______________________________________
- Survey 1
- Survey 2
- Survey 3
- _______________________________________
- Mar Surveys Due: 1
- _______________________________________
- Survey 1
- etc.
- Does anyone have any ideas on how to perform a grouped recordcount and
- output it as noted?
- ============================================
- Gary L. Alford
- Supplier Product Assurance
- Bell Helicopter Textron
- Phone: (817) 280-6233
- Fax: (817) 278-6233
- mailto:[EMAIL PROTECTED]
- ============================================
- -------------------------------------------------------------------------
- This email server is running an evaluation copy of the MailShield anti-
- spam software. Please contact your email administrator if you have any
- questions about this message. MailShield product info: www.mailshield.com
- -----------------------------------------------
- To post, send email to [EMAIL PROTECTED]
- To subscribe / unsubscribe: http://www.dfwcfug.org
American Contractors Insurance Group
Certified Macromedia ColdFusion Web Application Developer
972.687.9445 ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com
