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]
============================================
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
