|
|
On Feb 22, 5:55 pm, "semi star gazer" <webforumsu...@xxxxxxxxxxxxxx>
wrote:
> Hi, I have a query that is pulling a field called "Total" from 20 different
> tables (Week1 - Week20). When I try to output the results as #Week1.Total#,
> for
> example, I get an error saying that Total is not defined in Week1. I also
> tried
> totalHours.Week1.Total (totalHours is the name of my query), but get the same
> thing - Week1.Total is not defined in totalHours. Please help! Thanks!
>
> <cfquery name="totalHours" datasource="###">
> SELECT
> request.ID, request.FirstName, request.LastName, request.sem,
> request.year,
> request.banner,
> Week1.ID, Week1.Total,
> Week2.ID, Week2.Total,
> Week3.ID, Week3.Total,
> Week4.ID, Week4.Total,
> Week5.ID, Week5.Total,
> Week6.ID, Week6.Total,
> Week7.ID, Week7.Total,
> Week8.ID, Week8.Total,
> Week9.ID, Week9.Total,
> Week10.ID, Week10.Total,
> Week11.ID, Week11.Total,
> Week12.ID, Week12.Total,
> Week13.ID, Week13.Total,
> Week14.ID, Week14.Total,
> Week15.ID, Week15.Total,
> Week16.ID, Week16.Total,
> Week17.ID, Week17.Total,
> Week18.ID, Week18.Total,
> Week19.ID, Week19.Total,
> Week20.ID, Week20.Total
> FROM
> request, Week1, Week2, Week3, Week4, Week5, Week6, Week7,
> Week8, Week9,
> Week10, Week11, Week12, Week13, Week14, Week15, Week16, Week17, Week18,
> Week19,
> Week20
> WHERE
> request.ID = Week1.ID AND request.ID = Week2.ID AND
> request.ID = Week3.ID
> AND request.ID = Week4.ID AND request.ID = Week5.ID AND request.ID = Week6.ID
> AND request.ID = Week7.ID AND request.ID = Week8.ID AND request.ID = Week9.ID
> AND request.ID = Week10.ID AND request.ID = Week11.ID AND request.ID =
> Week12.ID AND request.ID = Week13.ID AND request.ID = Week14.ID AND request.ID
> = Week15.ID AND request.ID = Week16.ID AND request.ID = Week17.ID AND
> request.ID = Week18.ID AND request.ID = Week19.ID AND request.ID = Week20.ID
> AND request.sem = 'Fall' AND request.year = '2007'
> ORDER BY
> request.LastName, request.FirstName
> </cfquery>
>
> <cfoutput>
> <cfloop query="totalHours">
> <tr>
> <td>#ID#</td>
> <td>#banner#</td>
> <td>#LastName#, #FirstName#</td>
> <td>#Week1.Total#</td>
> <td>#Week2.Total#</td>
> <td>#Week3.Total#</td>
> <td>#Week4.Total#</td>
> <td>#Week5.Total#</td>
> <td>#Week6.Total#</td>
> <td>#Week7.Total#</td>
> <td>#Week8.Total#</td>
> <td>#Week9.Total#</td>
> <td>#Week10.Total#</td>
> <td>#Week11.Total#</td>
> <td>#Week12.Total#</td>
> <td>#Week13.Total#</td>
> <td>#Week14.Total#</td>
> <td>#Week15.Total#</td>
> <td>#Week16.Total#</td>
> <td>#Week17.Total#</td>
> <td>#Week18.Total#</td>
> <td>#Week19.Total#</td>
> <td>#Week20.Total#</td>
> <td><div align="center"><strong>Total</strong></div></td>
> </tr>
> </cfloop>
> </cfoutput>
1) Normalise your DB
2) You will never have to face a problem like this...
|
|