Table: Teams       TeamID -- autonumber field, primary key       TeamName -- text field       Wins -- numeric field       Losses -- numeric field       ... Table: Players       PlayerID -- autonumber field, primary key       TeamID -- numeric, foreign key to Teams       PlayerName -- text       ...

Each of those tables might have many more fields, of course, but those are enough to illustrate the concepts herein. And if you do not have your data arranged in this way--if you have some clumsier organization that doesn't meet the rules of database normalization--then you need to reorganize the data before you start, more than likely. (And do a search with google for "Database Normalization Techniques" if you need help on this topic. There are tons of material on it and it would be redundant to touch on it here!)

Anyway, given those two tables, what can you do with them?

Almost the biggest mistake you can make--and yet the method that we tend to most often see beginners attempting--is to open up the first table and then, as you move to each new record in that table, open up another recordset where you select only the subcategory records that match the given category. Something like this kind of code:

<% ' BAD CODE! DO NOT DO THIS! Set teamRS = conn.Execute("SELECT * FROM Teams ORDER BY TeamName") Response.Write "TEAM " & teamRS("TeamName") Do Until teamRS.EOF     Set playerRS = conn.Execute("SELECT * FROM Players WHERE TeamID=" & teamRS("TeamID")     Do Until playerRS.EOF         Response.Write " -- PLAYER " & playersRS("PlayerName")         playersRS.MoveNext     Loop     playersRS.Close     teamRS.MoveNext Loop %>

This works, but it puts a pretty big strain on the performance of your database server! If you have 30 teams, you have to open up 31 recordsets.

Let's see if we can do it all with one recordset!

<% SQL = "SELECT * FROM Teams, Players " _         & "WHERE Players.TeamID = Teams.TeamID " _         & "ORDER BY Teams.TeamName, Players.PlayerName" Set RS = conn.Execute( SQL ) priorTeam = "" Do Until RS.EOF     curTeam = RS("TeamName")     If curTeam <> priorTeam Then         If priorTeam <> "" Then             Response.Write "</UL>"         End If         Response.Write "TEAM: " & curTeam & "<UL>"         priorTeam = curTeam     End If     Response.Write "<LI>" & RS("PlayerName")     RS.MoveNext Loop Response.Write "</UL>" ' clean up the tags! %>

Let's look at how that code works.

First, we use a JOIN to get the information from both tables at once. (Yes, that is a JOIN. If you are an Access user who is used to seeing the keywords INNER JOIN, be assured that this is simply another way of expressing the same thing.) The important thing here is the ORDER BY clause that we put there! By ordering first by team name and then by player name, we ensure that all the players in the "Angels" will appear before all the players in the "Mariners". And, further, all the players will appear in alphabetical order within the team grouping.

We get an ADODB.RecordSet with all the players and all the teams and we are ready to process it. First, though, we initialize our priorTeam variable to a blank string.

We start with the first record. We get the name of the team from the record. Does it match the name of the prior team? (Well, of course not! There isn't any prior team!) A special test: Is the name of the prior team blank? Yes? Then do nothing. In any case, since we are processing a new team, output the team name along with an opening list delimiter. And then "remember" that this is the prior team! And we're done with the change-of-team processing.

So we output the name of the player that this record refers to and go to the next record.

Back at the top of our loop: We get the name of the team from the record. Does it match the name of the prior team? Let's assume it does. So we skip the change-of-team code and go to the next record. And this paragraph repeats until we finish the first team.

Finally, we get to a record where the team is not the same as the one we remembered in priorTeam. Is the name of the prior team blank? Not any more, so output a closing list delimiter tag. And, again, we output the new team name and an opening list delimiter and proceed to work with the players of this next team.

And we continue all this until we run out of records.

See that? It's not hard! You only had to keep track of one extra thing: the name of the team the prior player in the recordset was a member of. This is clean, simple, and fast code. In almost all cases, there is no reason not to go this way.

"He said 'almost all'! What is he hiding!"

Suppose that you have huge amount of information stored in each record of the "outer" (or "main") table. Perhaps a lengthy history of the team. Or maybe a ".gif" image of the team picture. With the technique given above, you'll be duplicating that huge chunk of data in each record of the recordset! This is not a good thing. What can we do?

The simple answer is that we can do part of the JOIN operation ourself, in VBScript coding!

<% ' add this recordset! teamSQL = "SELECT * FROM Teams ORDER BY Teams.TeamName" Set teamRS = conn.Execute( teamSQL ) ' same as before except we only get one field from Team... playerSQL = "SELECT Teams.TeamName, Players.* " _         & "FROM Teams, Players " _         & "WHERE Players.TeamID = Teams.TeamID " _         & "ORDER BY Teams.TeamName, Players.PlayerName" Set RS = conn.Execute( playerSQL ) priorTeam = "" Do Until RS.EOF     curTeam = RS("TeamName")     If curTeam <> priorTeam Then         If priorTeam <> "" Then             Response.Write "</UL>"         End If         Response.Write "TEAM: " & curTeam & "<UL>"         priorTeam = curTeam         ' AND HERE IS THE NEW CODE!         If curTeam <> teamRS("TeamName") Then             Response.Write "Something went badly wrong!"             Response.End         End If         ' (field names are just for demo purposes)         Response.Write "This team was established in " _                 & teamRS("startYear") _                 & " by " & teamRS("founder") _                 & " and its history is " _                 & teamRS("history") & "<P>"         teamRS.MoveNext         ' end of added code     End If     Response.Write "<LI>" & RS("PlayerName")     RS.MoveNext Loop Response.Write "</UL>" ' clean up the tags! %>

See what we've done? We've ensured that the primary sort in both recordsets (the name of the team, in this example) is the same. And then we have limited the data we get from the "outer" table to just the one field (again, the name of the team). Then, as we traipse through the RS recordset in exactly the same manner as before, we pull the needed extra information from the main table only as we change teams! And we only do the teamRS.MoveNext at that time, so (if we did everything right!) we will never get that error message and end prematurely.

Whew! If it looks complicated to you on first glance, study it a while longer. It really isn't that hard to adapt to whatever situation you might need it for.

If you'd like to see an example of the first and simpler form of this code, go to http://www.ClearviewDesign.com/NEWBIE and look for the "Special Category and Subcategory Display Demo" on that page.