Let me reply to my own question (copied in full at the bottom of this
message) to try and refine the problem. I've been continuing to work on this
and maybe this is a better way to start asking my question:
tblOne
year .... player .... points
1997 .... john .... 15
1997 .... pete .... 12
1998 .... john .... 10
1998 .... pete .... 9
1999 .... john .... 8
1999 .... pete .... 14
1999 .... joe .... 12
2000 .... john .... 11
2000 .... pete .... 12
2000 .... john .... 12
2000 .... joe .... 8
2000 .... sam .... 9
Given this table, how do you run through the whole thing, get each players'
cumulative points for all years, and then <cfoutput> it in a format like:
player .... pts
john .... 50
pete .... 45
joe .... 42
sam .... 15
(Math's not right - didn't bother to do the calculations)
Thanks - maybe if I see the answer to this I can figure out everything else
that I've asked in my question below.
Paul Sinclair
> From: Paul Sinclair [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 27, 2000 7:09 AM
> To: CF-Talk
> Subject: Getting/manipulating data for multiple records, multiple tables
>
>
> I'm having trouble figuring out how to use a SQL select statement to get
> data for multiple records from multiple tables and then do some
> math on each
> of the individual records for <cfoutput>.
>
> I have a db with results from an annual golf event. There are 3 tables
> containing the data I need: tblOne with results from day one; tblTwo with
> results from day two; tblThree with demographic info (name, club,
> handicap,
> etc.). Here is a simplified version of what I need to do:
>
> 1.
> SELECT from tblThree to get demographic info
> SELECT from tblOne getting points scored by each golfer on day one
> SELECT from tblTwo getting points scored by each golfer on day two
>
> 2.
> For each golfer, do some math using data from the above SELECTs,
> for example
> (dayOne pts + dayTwo pts)
>
> 3.
> <CFOUTPUT> the results for each golfer </cfoutput>
>
> I can run all this through just fine if I feed a player name to the
> template. But I need to compile an overall chart that contains
> the data for
> every golfer. What I'm struggling with is how to take each golfer's data
> sequentially and do the math on it. I've tried using <CFLOOP> to loop over
> the <CFQUERY> results for each golfer, but I haven't figured the magic
> formula yet.
>
> I am probably overlooking something simple - not seeing the forest through
> the trees. Any help would be great.
>
> Paul Sinclair
>
>
>
>
>
> ------------------------------------------------------------------
> ------------------------------
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
> or send a message with 'unsubscribe' in the body to
> [EMAIL PROTECTED]
>
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
