just wanted to thank every one.
i couldn't get what you where talking about to work.
below is what i did. which works, but is a ton of ugly code.
if you can explain to me a better way, i would still appreciate it.
if we had a budget, i might take a SQL class.
grrrrrr................thanks again
-paul
<cfquery name="Admin" datasource=#MM_Marketing_Survey_DSN#
username=#MM_Marketing_Survey_USERNAME#
password=#MM_Marketing_Survey_PASSWORD#>
SELECT
AVG(tbl_Market_Seller_Assessment.Studio_01) AS Studio_01q,
AVG(tbl_Market_Seller_Assessment.Studio_02) AS Studio_02q,
AVG(tbl_Market_Seller_Assessment.Studio_03) AS Studio_03q,
AVG(tbl_Market_Seller_Assessment.Studio_04) AS Studio_04q,
AVG(tbl_Market_Seller_Assessment.Studio_05) AS Studio_05q,
AVG(tbl_Market_Seller_Assessment.Studio_06) AS Studio_06q
FROM tbl_Market_Seller_Assessment, tbl_Employee, tbl_Studio
WHERE tbl_Market_Seller_Assessment.EmployeeID = tbl_Employee.EmployeeID
AND tbl_Studio.StudioID = tbl_Employee.StudioID
AND tbl_Studio.StudioID = 2
OR tbl_Studio.StudioID = 3
OR tbl_Studio.StudioID = 5
OR tbl_Studio.StudioID = 7
OR tbl_Studio.StudioID = 9
OR tbl_Studio.StudioID = 11
OR tbl_Studio.StudioID = 13
</cfquery>
<cfquery name="a01" datasource=#MM_Marketing_Survey_DSN#
username=#MM_Marketing_Survey_USERNAME#
password=#MM_Marketing_Survey_PASSWORD#>
SELECT
AVG(tbl_Market_Seller_Assessment.Studio_01) AS Studio_01q,
AVG(tbl_Market_Seller_Assessment.Studio_02) AS Studio_02q,
AVG(tbl_Market_Seller_Assessment.Studio_03) AS Studio_03q,
AVG(tbl_Market_Seller_Assessment.Studio_04) AS Studio_04q,
AVG(tbl_Market_Seller_Assessment.Studio_05) AS Studio_05q,
AVG(tbl_Market_Seller_Assessment.Studio_06) AS Studio_06q
FROM tbl_Market_Seller_Assessment, tbl_Employee, tbl_Studio
WHERE tbl_Market_Seller_Assessment.EmployeeID = tbl_Employee.EmployeeID
AND tbl_Studio.StudioID = tbl_Employee.StudioID
AND tbl_Studio.StudioID = 31
</cfquery>
<cfquery name="a23" datasource=#MM_Marketing_Survey_DSN#
username=#MM_Marketing_Survey_USERNAME#
password=#MM_Marketing_Survey_PASSWORD#>
SELECT
AVG(tbl_Market_Seller_Assessment.Studio_01) AS Studio_01q,
AVG(tbl_Market_Seller_Assessment.Studio_02) AS Studio_02q,
AVG(tbl_Market_Seller_Assessment.Studio_03) AS Studio_03q,
AVG(tbl_Market_Seller_Assessment.Studio_04) AS Studio_04q,
AVG(tbl_Market_Seller_Assessment.Studio_05) AS Studio_05q,
AVG(tbl_Market_Seller_Assessment.Studio_06) AS Studio_06q
FROM tbl_Market_Seller_Assessment, tbl_Employee, tbl_Studio
WHERE tbl_Market_Seller_Assessment.EmployeeID = tbl_Employee.EmployeeID
AND tbl_Studio.StudioID = tbl_Employee.StudioID
AND tbl_Studio.StudioID = 33
</cfquery>
<cfquery name="a04" datasource=#MM_Marketing_Survey_DSN#
username=#MM_Marketing_Survey_USERNAME#
password=#MM_Marketing_Survey_PASSWORD#>
SELECT
AVG(tbl_Market_Seller_Assessment.Studio_01) AS Studio_01q,
AVG(tbl_Market_Seller_Assessment.Studio_02) AS Studio_02q,
AVG(tbl_Market_Seller_Assessment.Studio_03) AS Studio_03q,
AVG(tbl_Market_Seller_Assessment.Studio_04) AS Studio_04q,
AVG(tbl_Market_Seller_Assessment.Studio_05) AS Studio_05q,
AVG(tbl_Market_Seller_Assessment.Studio_06) AS Studio_06q
FROM tbl_Market_Seller_Assessment, tbl_Employee, tbl_Studio
WHERE tbl_Market_Seller_Assessment.EmployeeID = tbl_Employee.EmployeeID
AND tbl_Studio.StudioID = tbl_Employee.StudioID
AND tbl_Studio.StudioID = 37
</cfquery>
<cfquery name="a56" datasource=#MM_Marketing_Survey_DSN#
username=#MM_Marketing_Survey_USERNAME#
password=#MM_Marketing_Survey_PASSWORD#>
SELECT
AVG(tbl_Market_Seller_Assessment.Studio_01) AS Studio_01q,
AVG(tbl_Market_Seller_Assessment.Studio_02) AS Studio_02q,
AVG(tbl_Market_Seller_Assessment.Studio_03) AS Studio_03q,
AVG(tbl_Market_Seller_Assessment.Studio_04) AS Studio_04q,
AVG(tbl_Market_Seller_Assessment.Studio_05) AS Studio_05q,
AVG(tbl_Market_Seller_Assessment.Studio_06) AS Studio_06q
FROM tbl_Market_Seller_Assessment, tbl_Employee, tbl_Studio
WHERE tbl_Market_Seller_Assessment.EmployeeID = tbl_Employee.EmployeeID
AND tbl_Studio.StudioID = tbl_Employee.StudioID
AND tbl_Studio.StudioID = 52
</cfquery>
<cfquery name="a78" datasource=#MM_Marketing_Survey_DSN#
username=#MM_Marketing_Survey_USERNAME#
password=#MM_Marketing_Survey_PASSWORD#>
SELECT
AVG(tbl_Market_Seller_Assessment.Studio_01) AS Studio_01q,
AVG(tbl_Market_Seller_Assessment.Studio_02) AS Studio_02q,
AVG(tbl_Market_Seller_Assessment.Studio_03) AS Studio_03q,
AVG(tbl_Market_Seller_Assessment.Studio_04) AS Studio_04q,
AVG(tbl_Market_Seller_Assessment.Studio_05) AS Studio_05q,
AVG(tbl_Market_Seller_Assessment.Studio_06) AS Studio_06q
FROM tbl_Market_Seller_Assessment, tbl_Employee, tbl_Studio
WHERE tbl_Market_Seller_Assessment.EmployeeID = tbl_Employee.EmployeeID
AND tbl_Studio.StudioID = tbl_Employee.StudioID
AND tbl_Studio.StudioID = 41
</cfquery>
<cfquery name="Q_Marketing_Survey" datasource=#MM_Marketing_Survey_DSN#
username=#MM_Marketing_Survey_USERNAME#
password=#MM_Marketing_Survey_PASSWORD#>
SELECT AVG(Studio_01) AS Studio_01q, AVG(Studio_02) AS Studio_02q,
AVG(Studio_03)
AS Studio_03q, AVG(Studio_04) AS Studio_04q, AVG(Studio_05) AS Studio_05q,
AVG(Studio_06)
AS Studio_06q, AVG(Firm_01) AS Firm_01q, AVG(Firm_02) AS Firm_02q,
AVG(Firm_03)
AS Firm_03q, AVG(Firm_04) AS Firm_04q, AVG(Firm_05) AS Firm_05q,
AVG(Firm_06)
AS Firm_06q, AVG(Personal_01) AS Personal_01q, AVG(Personal_02) AS
Personal_02q,
AVG(Personal_03) AS Personal_03q, AVG(Personal_04) AS Personal_04q,
AVG(Personal_05)
AS Personal_05q, AVG(Personal_06) AS Personal_06q FROM
tbl_Market_Seller_Assessment
</cfquery>
<cfquery name="rs_total" datasource=#MM_Marketing_Survey_DSN#
username=#MM_Marketing_Survey_USERNAME#
password=#MM_Marketing_Survey_PASSWORD#>
SELECT * FROM tbl_Market_Seller_Assessment
</cfquery>
----------------------------------------------------------------------------
-------------
<table>
<tr>
<td bgcolor="#CCFFCC" width="26"> <b>Q #</b></td>
<td bgcolor="#CCFFCC"> <b>Studio</b></td>
<td bgcolor="#CCFFCC"> <b>Avg </b></td>
<td bgcolor="#CCFFCC"> <b>Prin</b></td>
<td bgcolor="#CCFFCC"> <b>VP</b></td>
<td bgcolor="#CCFFCC"> <b>ML</b></td>
<td bgcolor="#CCFFCC"> <b>Support</b></td>
<td bgcolor="#CCFFCC"> <b>Other</b></td>
<td bgcolor="#CCFFCC"> <b>1</b></td>
<td bgcolor="#CCFFCC"> <b>23</b></td>
<td bgcolor="#CCFFCC"> <b>4</b></td>
<td bgcolor="#CCFFCC"> <b>56</b></td>
<td bgcolor="#CCFFCC"> <b>78</b></td>
<td bgcolor="#CCFFCC"> <b>A</b></td>
<td bgcolor="#CCFFCC"> <b>P</b></td>
</tr>
<TR ALIGN="left" VALIGN="top">
<td width="26" bgcolor="#CCFFCC">01</td>
<td>My Studio is effective in marketing. </td>
<!--- #NumberFormat(Round(Score * 100))#% --->
<TD> <cfoutput>#LSNumberFormat(Q_Marketing_Survey.Studio_01q,
',9999')#</cfoutput></TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<td> <cfoutput>#LSNumberFormat(a01.Studio_01q,
',9999')#</cfoutput></td>
<td> <cfoutput>#LSNumberFormat(a23.Studio_01q,
',9999')#</cfoutput></td>
<td> <cfoutput>#LSNumberFormat(a04.Studio_01q,
',9999')#</cfoutput></td>
<td> <cfoutput>#LSNumberFormat(a56.Studio_01q,
',9999')#</cfoutput></td>
<td> <cfoutput>#LSNumberFormat(a78.Studio_01q,
',9999')#</cfoutput></td>
<td> <cfoutput>#LSNumberFormat(admin.Studio_01q,
',9999')#</cfoutput></td>
<TD> </TD>
</TR>
<TR ALIGN="left" VALIGN="top">
<td width="26" bgcolor="#CCFFCC">02</td>
<td>My Studio has the materials and resume to enable me to win the
commissions
I pursue</td>
<TD><cfoutput>#LSNumberFormat(Q_Marketing_Survey.Studio_02q,
',9999')#</cfoutput></TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<td><cfoutput>#LSNumberFormat(a01.Studio_02q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a23.Studio_02q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a04.Studio_02q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a56.Studio_02q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a78.Studio_02q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(admin.Studio_02q,
',9999')#</cfoutput></td>
<TD> </TD>
</TR>
<TR ALIGN="left" VALIGN="top">
<td width="26" bgcolor="#CCFFCC">03</td>
<td>My Studio regularly uses a rational no-go/how-go system.</td>
<TD><cfoutput>#LSNumberFormat(Q_Marketing_Survey.Studio_03q,
',9999')#</cfoutput></TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<td><cfoutput>#LSNumberFormat(a01.Studio_03q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a23.Studio_03q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a04.Studio_03q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a56.Studio_03q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a78.Studio_03q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(admin.Studio_03q,
',9999')#</cfoutput></td>
<TD> </TD>
</TR>
<TR ALIGN="left" VALIGN="top">
<td width="26" bgcolor="#CCFFCC">04</td>
<td>My Studio is able to generate a steady supply of qualified leads.</td>
<TD><cfoutput>#LSNumberFormat(Q_Marketing_Survey.Studio_04q,
',9999')#</cfoutput></TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<td><cfoutput>#LSNumberFormat(a01.Studio_04q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a23.Studio_04q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a04.Studio_04q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a56.Studio_04q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a78.Studio_04q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(admin.Studio_04q,
',9999')#</cfoutput></td>
<TD> </TD>
</TR>
<TR ALIGN="left" VALIGN="top">
<td width="26" bgcolor="#CCFFCC">05</td>
<td>Other marketer/sellers in my Studio are effective</td>
<TD><cfoutput>#LSNumberFormat(Q_Marketing_Survey.Studio_05q,
',9999')#</cfoutput></TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<td><cfoutput>#LSNumberFormat(a01.Studio_05q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a23.Studio_05q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a04.Studio_05q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a56.Studio_05q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a78.Studio_05q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(admin.Studio_05q,
',9999')#</cfoutput></td>
<TD> </TD>
</TR>
<TR ALIGN="left" VALIGN="top">
<td width="26" bgcolor="#CCFFCC">06</td>
<td> My Studio effectively to convert pre-contract wins to signed
contracts.
</td>
<TD><cfoutput>#LSNumberFormat(Q_Marketing_Survey.Studio_06q,
',9999')#</cfoutput></TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<TD> </TD>
<td><cfoutput>#LSNumberFormat(a01.Studio_06q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a23.Studio_06q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a04.Studio_06q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a56.Studio_06q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(a78.Studio_06q,
',9999')#</cfoutput></td>
<td><cfoutput>#LSNumberFormat(admin.Studio_06q,
',9999')#</cfoutput></td>
<TD> </TD>
</TR>
</table>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
