Hey Mike, I'm not much of a sports fan, so you'll have to forgive me... GAME is independant of team? ... You might create a cross-reference table with 3 columns, TeamID, GameID and GameIndex ... What this would show is the list of games for a given team ( where the team is either in the team or opponent column ) ... That way, you could get the surface from 3 games ago by simply subtracting 3 from the gameindex for the given team and finding that game in the games table.
It'd also be reasonably easy to populate the cross-reference table from the existing data. Otherwise you could use a complex sql query to actually draw the data from the existing table... something like this might work: SELECT * FROM games WHERE ID = (SELECT MIN ID FROM games WHERE ID IN (SELECT TOP #gamesback# ID FROM games WHERE team = '#myteam#' or opponent = '#myteam#' AND ID < #currentgameid# ORDER BY ID DESC) ) hope this helps, Isaac www.turnkey.to 954-776-0046 ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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
