Hi Michael, You may find it becoming increasingly difficult to maintain that sort of design in the future...Perhaps you should take a look at breaking it out into multiple tables then use SQL joins to query the data.
For example, a table for teams and another for "GameInstances". In the first table is you have all the teams represented by a unique ID. The second table tracks every game along with dates and other specifics and have two ID columns linked back to the team table to link the two teams that played that particular match. Hope this helps, Stace -----Original Message----- From: Michael Salemi [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 22, 2002 12:29 PM To: CF-Talk Subject: Can I do this with a Query? I have a database of NFL data. The majority of the data is stored in a single table, with each row of data containing various info on each game played. A very simplified version looks like this: ID TEAM OPPONENT GAME SEASON SURFACE 5 NY Giants Dallas 10 2000 G 6 Pittsburgh St Louis 11 2000 T etc. I am looking to create a query which will display the number of records where certain conditions are true. Its simple when the criteria for sorting is all contained on the same row. The twist occurs when I want to look at games where a TEAM played on a certain SURFACE last game (or for that matter 5 games ago). This would not be a problem for me if I made new columns in the database such as SURFACELASTWEEK, SURFACETWOWEEKSAGO, SURFACETHREEWEEKSAGO, etc., but with over 50 details about each game that would be a monstrous task and difficult to maintain. I was thinking maybe I could just add a few new columns that merely had the reference ID of the last game, 2 games ago, 3 games ago, etc. But still I'm not sure how to write this query. This is the abbreviated version of what I have: <CFQUERY DATASOURCE="nfl" name="SearchResultsQuery"> SELECT SEASON, GAME, DAY FROM Data WHERE (SEASON BETWEEN #SeasonLow# AND #SeasonHigh#) AND (SURFACE = 'G') AND <!--- what goes here? ----> </CFQUERY> Sorry for the long email, but hopefully what I'm looking for is clear. Thanks in advance for any help/advice. Mike ______________________________________________________________________ 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
