Tasha Williams wrote:
>I am brand new to cold fusion and am trying to make a page that lists
>statistics for 12 realtors in tabular format, but for some reason the
>statistics tables are listed 12 times instead of only once. I think that
>there is either something wrong with the cfloop or cfoutput, but I have not
>been able to figure it out with my limited knowledge. I've asked for help
>from some in my class and my professor, but all are stumped. Can anyone help?
> The source is listed below and the page is located at
>http://students.libsci.sc.edu/williamst/WilliamsRealtors/agentStatistics.cfm
>Thanks,
>T Wms.
>
Morning Tasha,
Ray is correct. You need to collapse your 3 queries into one query that
gets all the agents and their property statistics all at once.
Once you've done that you only need one cfoutput.
I have to run, but here's probably what you need in terms of query and
code. The query should get one row per agent from the database with all
the details and stats in one go.
When I took your code apart I found a couple of additional html table
tags and some badly nested code. Always make sure you run a code
formater in DWMX or cfesclipse or just make sure you indent code
correctly and it will help you make sure that your code is correct.
Sorry for the rushed reply
regards
Stephen
<cfquery name="AgentStats" datasource="williamst">
SELECT AgentID,
FirstName,
MiddleInitial,
LastName,
count(Price) as Num,
sum(Price) as Total,
avg(Price) as Avg,
max(Price) as Max,
min(Price) as Min
FROM Agents
INNER JOIN Properties ON Agents.AgentID = Properties.AgentID
GROUP BY AgentID, FirstName, MiddleInitial, LastName
ORDER BY LastName
</cfquery>
<HTML>
<HEAD>
<TITLE>Price Statistics</TITLE>
<STYLE>
TD {
font-family : "Times New Roman"; color : "white";
}
H3 {
font-family : "Times New Roman"; color : "white";
}
</STYLE>
</HEAD>
<BODY bgcolor="#000066">
<HR align="center" color="white" width="300">
<CFOUTPUT query="AgentStats">
<H3 align="center">
Price Statistics for Agent
-<CFOUTPUT>#FirstName#</CFOUTPUT><CFOUTPUT>#MiddleInitial#</CFOUTPUT><CFOUTPUT>#LastName#</CFOUTPUT></H3>
<HR align="center" color="white" width="300">
<TABLE align="center" border="1" bordercolor="white"
cellspacing="0">
<TR align="right">
<TD>Number of properties:</TD><TD>#Num#</TD>
</TR>
<TR align="right">
<TD>Total price:</TD><TD>#Dollarformat(Total)#</TD>
</TR>
<TR align="right">
<TD>Average price:</TD>
<TD>#Dollarformat(Avg)#</TD>
</TR>
<TR align="right">
<TD>Maximum price:</TD>
<TD>#Dollarformat(Max)#</TD>
</TR>
<TR align="right">
<TD>Minimum price:</TD>
<TD>#Dollarformat(Min)#</TD>
</TR>
</TABLE>
<HR align="center" color="white" width="300"></B>
</CFOUTPUT>
</BODY>
</HTML>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1196
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
