I have written a query as:
Code:
SELECT SubTeamID, Teams.TeamName, P1.FirstName, P1.LastName, P1.NameSuffix, P2.FirstName, P2.LastName, P2.NameSuffix
FROM Teams, TwoManTeamPlayers AS TMT, Players AS P1, Players AS P2
WHERE P1.PersonID=TMT.[Player-1] and
P2.PersonID=TMT.[Player-2] and
P1.TeamID=Teams.TeamID
ORDER BY Teams.TeamName, TMT.SubTeamID;
However, whenever I run it, it sorts on P1.FirstName not TeamName. I have tried changing the order of the tables in the FROM clause with no success. I have also tried an ORDER BY with numeric indicators instead of actual field names. Still doesn't work. I get all the data, but it always sorts on player's first name instead of team name.