I've already tried searching posts, but haven't found any similiar solutions 
to this. I'm new to SQL and am using Access 2007 as my development 
environment. I'm trying to keep my solution as simple as possible.

I have two relationships between two tables as follows:
GamesTable:
ID - Autonumber
Team1ID - Number (Relationship w/ TeamsTable ID)
Team2ID - Number (Relationship w/ TeamsTable ID)
Team1Score - Number
Team2Score - Number
Team1Points - Number
Team2Points - Number

TeamsTable:
ID - Autonumber
TeamName - Text

The question I'm trying to answer is to find the team that won the league. 
I've been successful with finding the sum of scores for each team. The 
problem lies in when I try to find out who has the most points. My code is 
as follows:

SELECT Sum(Games.Team1Points) AS SumOfTeam1Points
FROM Games
GROUP BY Games.Team1ID;

Attempting to use MAX in my select statement around the SUM function is 
invalid, though I'm not sure why.

Since I need to find the name of the team that won, I'm of the belief that I 
need to return the correct TeamID for the team that won back to my 
TeamsTable so I can then get the name. I'm under the impression I need a 
subquery here, but I'm lost as to how I can return the ID of the team that 
has the most points once I've summed their respective points.

Any help appreciated.

Thank you,

Tony 

Tony:

Einstein once said, when asked about explaining the theory of general 
relativity, that you can make things as simple as possible, but no more so.  
So here goes.

Firstly your GamesTable is not well structured.  Having separate columns for 
each team, score and points is what’s known in the jargon as ‘encoding data 
as column headings’, whereas in a relational database data should only be 
stored as values at column positions in rows in tables; this is known as the 
‘information principle’.  So the table should be decomposed into a Games 
table with columns GameID, GameDate, Venue etc, and a GameTeams table with 
columns GameID (a foreign key in this case) TeamID, Score, Points and any 
others you need such as one to indicate if its the Home or Away team.   The 
primary key of this table would be a composite one made up of the GameID and 
TeamID columns.  The table in fact models the many-to-many relationship type 
between Teams and Games.

With the points in one column in a table you can then easily sum the points 
per team with:

SELECT Team, SUM(Points) AS TotalPoints
FROM GameTeams INNER JOIN Teams
ON GameTeams.TeamID = Teams.TeamID
GROUP BY Team;

To show only the team(s) with the highest number of points you can sort the 
results in descending order and return the TOP 1 row(s):

SELECT TOP 1 Team, SUM(Points) AS TotalPoints
FROM GameTeams INNER JOIN Teams
ON GameTeams. TeamID = Teams.TeamID
GROUP BY Team
ORDER BY SUM(Points) DESC;

You can of course include any other columns from the Teams table in the 
query so long as you include them in its GROUP BY Clause as well as in the 
SELECT clause.

Ken Sheridan
Stafford, England 

"Tony" wrote:

> I've already tried searching posts, but haven't found any similiar solutions 
> to this. I'm new to SQL and am using Access 2007 as my development 
> environment. I'm trying to keep my solution as simple as possible.
> 
> I have two relationships between two tables as follows:
> GamesTable:
> ID - Autonumber
> Team1ID - Number (Relationship w/ TeamsTable ID)
> Team2ID - Number (Relationship w/ TeamsTable ID)
> Team1Score - Number
> Team2Score - Number
> Team1Points - Number
> Team2Points - Number
> 
> TeamsTable:
> ID - Autonumber
> TeamName - Text
> 
> The question I'm trying to answer is to find the team that won the league. 
> I've been successful with finding the sum of scores for each team. The 
> problem lies in when I try to find out who has the most points. My code is 
> as follows:
> 
> SELECT Sum(Games.Team1Points) AS SumOfTeam1Points
> FROM Games
> GROUP BY Games.Team1ID;
> 
> Attempting to use MAX in my select statement around the SUM function is 
> invalid, though I'm not sure why.
> 
> Since I need to find the name of the team that won, I'm of the belief that I 
> need to return the correct TeamID for the team that won back to my 
> TeamsTable so I can then get the name. I'm under the impression I need a 
> subquery here, but I'm lost as to how I can return the ID of the team that 
> has the most points once I've summed their respective points.
> 
> Any help appreciated.
> 
> Thank you,
> 
> Tony 
> 
>

Tony:

An unwanted space crept into my reply.  The query should be:

SELECT TOP 1 Team, SUM(Points) AS TotalPoints
FROM GameTeams INNER JOIN Teams
ON GameTeams.TeamID = Teams.TeamID
GROUP BY Team
ORDER BY SUM(Points) DESC;

You can of course include any other columns from the Teams table in the 
query so long as you include them in its GROUP BY Clause as well as in the 
SELECT clause.

Ken Sheridan
Stafford, England 

Thank you Ken. I'll have to digest this more once I've had some breakfast. 
Though I'm not sure I want to alter the design of the tables right now, I 
agree with you that I 'should' based on your reasoning. Thank you for 
explaining the why as well, no point in learning how to do something without 
knowing why.

Tony

"Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message 
news:0D7B90F9-74B0-46D2-8C33-62B13F6BB8FF@microsoft.com...
> Tony:
>
> An unwanted space crept into my reply.  The query should be:
>
> SELECT TOP 1 Team, SUM(Points) AS TotalPoints
> FROM GameTeams INNER JOIN Teams
> ON GameTeams.TeamID = Teams.TeamID
> GROUP BY Team
> ORDER BY SUM(Points) DESC;
>
> You can of course include any other columns from the Teams table in the
> query so long as you include them in its GROUP BY Clause as well as in the
> SELECT clause.
>
> Ken Sheridan
> Stafford, England
> 

SELECT TeamName, Sum(Games.Team1Points) as Score
FROM GAMES INNER JOIN TeamTable
ON Games.Team1Id = TeamsTable.TeamID
GROUP BY TeamName

HAVING Sum(Team1Points) =

    (SELECT Max(A.SumPoints)
    FROM
       (SELECT Sum(Games.Team1Points)SumPoints
       FROM Games
       GROUP BY Games.Team1ID) as A)


A real problem here is that your table structure for GamesTable makes it 
difficult to get all the points for any one team - since a team could be 
  referenced as either team1 or team2 and the relevant points could be 
stored in team1Score or Team2Score.

You could use a UNION query to normalize the date
SELECT Team1Id, Team1Score, Team1Points
FROM GamesTable
UNION ALL
SELECT Team2ID, Team2Score, Team2Points
FROM GamesTable

And substitute the Union query for the games table in the above query. 
A better structure might look like

Teams (Table)
TeamID
TeamName

Games (Table)
GameNumber
TeamID
GameDate
....

GameScores (Table)
GameNumber
TeamID
Score
Points

Then the query you want would be

SELECT TOP 1 Teams.TeamName
, Sum(GameScores.Points)
FROM Teams INNER JOIN GameScores
ON Teams.TeamID = GameScores.TeamID
Group By Teams.TeamName
ORDER BY Sum(GameScores.Points) Desc


'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Tony wrote:
> I've already tried searching posts, but haven't found any similiar 
> solutions to this. I'm new to SQL and am using Access 2007 as my 
> development environment. I'm trying to keep my solution as simple as 
> possible.
> 
> I have two relationships between two tables as follows:
> GamesTable:
> ID - Autonumber
> Team1ID - Number (Relationship w/ TeamsTable ID)
> Team2ID - Number (Relationship w/ TeamsTable ID)
> Team1Score - Number
> Team2Score - Number
> Team1Points - Number
> Team2Points - Number
> 
> TeamsTable:
> ID - Autonumber
> TeamName - Text
> 
> The question I'm trying to answer is to find the team that won the 
> league. I've been successful with finding the sum of scores for each 
> team. The problem lies in when I try to find out who has the most 
> points. My code is as follows:
> 
> SELECT Sum(Games.Team1Points) AS SumOfTeam1Points
> FROM Games
> GROUP BY Games.Team1ID;
> 
> Attempting to use MAX in my select statement around the SUM function is 
> invalid, though I'm not sure why.
> 
> Since I need to find the name of the team that won, I'm of the belief 
> that I need to return the correct TeamID for the team that won back to 
> my TeamsTable so I can then get the name. I'm under the impression I 
> need a subquery here, but I'm lost as to how I can return the ID of the 
> team that has the most points once I've summed their respective points.
> 
> Any help appreciated.
> 
> Thank you,
> 
> Tony

Hi John,

Thanks for your help here as well. You nailed it on my problem with having a 
team referenced as either team1 or team2 and their points could be stored in 
either team1Points or team2Points. So per your suggestions, and also Ken's, 
I'm in the process of restructuring my DB to work in manner more inline with 
proper relational DB design.

When attempting to run this, I am prompted with a dialog asking for the 
Teams.TeamID. If I enter 1, I get every TeamName and the score 7 for each 
(this is the max sum from data I have entered). Why is every team listed? If 
I alter it to the following, I still get the dialog prompt, but only for the 
team I'm interested in.

SELECT TOP 1 TeamName, Sum(Games.Team1Points) as Score
FROM GAMES INNER JOIN Teams
ON Games.Team1Id = Teams.TeamID
GROUP BY TeamName

HAVING Sum(Team1Points) =

    (SELECT Max(A.SumPoints)
    FROM
       (SELECT Sum(Games.Team1Points) AS SumPoints
       FROM Games
       GROUP BY Games.Team1ID) as A);

Thanks again for your help. It's nice to understand why SQL works the way it 
does. I'm finally starting to see how this subquery stuff works. The alias 
naming thing is a bit confusing reading the book (Modern Database Mgmt. By 
Jeffrey A. Hoffer, Prescott, McFadden). The book states how it's just a way 
to rename the column heading. Coming from a "intermediate" level 'C' 
programming background, I see though that you have used this alias to 
reference the collection. I've seen this before (namely for table names), 
and the way I relate to it is establishing a variable. E.g. int someNumber; 
Is this a properly analogy?

Tony


"John Spencer" <spencer@chpdm.umbc> wrote in message 
news:e9V$rnpOIHA.3400@TK2MSFTNGP03.phx.gbl...
>
>
> SELECT TeamName, Sum(Games.Team1Points) as Score
> FROM GAMES INNER JOIN TeamTable
> ON Games.Team1Id = TeamsTable.TeamID
> GROUP BY TeamName
>
> HAVING Sum(Team1Points) =
>
>    (SELECT Max(A.SumPoints)
>    FROM
>       (SELECT Sum(Games.Team1Points)SumPoints
>       FROM Games
>       GROUP BY Games.Team1ID) as A)
>
>
> A real problem here is that your table structure for GamesTable makes it 
> difficult to get all the points for any one team - since a team could be 
> referenced as either team1 or team2 and the relevant points could be 
> stored in team1Score or Team2Score.
>
> You could use a UNION query to normalize the date
> SELECT Team1Id, Team1Score, Team1Points
> FROM GamesTable
> UNION ALL
> SELECT Team2ID, Team2Score, Team2Points
> FROM GamesTable
>
> And substitute the Union query for the games table in the above query. A 
> better structure might look like
>
> Teams (Table)
> TeamID
> TeamName
>
> Games (Table)
> GameNumber
> TeamID
> GameDate
> ...
>
> GameScores (Table)
> GameNumber
> TeamID
> Score
> Points
>
> Then the query you want would be
>
> SELECT TOP 1 Teams.TeamName
> , Sum(GameScores.Points)
> FROM Teams INNER JOIN GameScores
> ON Teams.TeamID = GameScores.TeamID
> Group By Teams.TeamName
> ORDER BY Sum(GameScores.Points) Desc
>
>
> '====================================================
>  John Spencer
>  Access MVP 2002-2005, 2007
>  Center for Health Program Development and Management
>  University of Maryland Baltimore County
> '====================================================
>

Hi John,

One more question here. I'm trying to understand your design and the 
relationship between Teams, Games, GameScores.

Based on your design below, I am guessing the following:

Teams (Table)        Games (Table)            GameScores (Table)
TeamID                  TeamID                      TeamID
TeamName             GameNumber             GameNumber
                               GameDate                  Score
                                                                 Points

Games & GameScores have a relationship between TeamID in Teams Table. Each 
Team has many games and game scores.
Games and GameScores have a relationship between GameNumber. Each game 
having many game scores.

Is this correct?

Thanks,

Tony

"John Spencer" <spencer@chpdm.umbc> wrote in message 
news:e9V$rnpOIHA.3400@TK2MSFTNGP03.phx.gbl...
>
>
> SELECT TeamName, Sum(Games.Team1Points) as Score
> FROM GAMES INNER JOIN TeamTable
> ON Games.Team1Id = TeamsTable.TeamID
> GROUP BY TeamName
>
> HAVING Sum(Team1Points) =
>
>    (SELECT Max(A.SumPoints)
>    FROM
>       (SELECT Sum(Games.Team1Points)SumPoints
>       FROM Games
>       GROUP BY Games.Team1ID) as A)
>
>
> A real problem here is that your table structure for GamesTable makes it 
> difficult to get all the points for any one team - since a team could be 
> referenced as either team1 or team2 and the relevant points could be 
> stored in team1Score or Team2Score.
>
> You could use a UNION query to normalize the date
> SELECT Team1Id, Team1Score, Team1Points
> FROM GamesTable
> UNION ALL
> SELECT Team2ID, Team2Score, Team2Points
> FROM GamesTable
>
> And substitute the Union query for the games table in the above query. A 
> better structure might look like
>
> Teams (Table)
> TeamID
> TeamName
>
> Games (Table)
> GameNumber
> TeamID
> GameDate
> ...
>
> GameScores (Table)
> GameNumber
> TeamID
> Score
> Points
>
> Then the query you want would be
>
> SELECT TOP 1 Teams.TeamName
> , Sum(GameScores.Points)
> FROM Teams INNER JOIN GameScores
> ON Teams.TeamID = GameScores.TeamID
> Group By Teams.TeamName
> ORDER BY Sum(GameScores.Points) Desc
>
>
> '====================================================
>  John Spencer
>  Access MVP 2002-2005, 2007
>  Center for Health Program Development and Management
>  University of Maryland Baltimore County
> '====================================================

Hi Ken,

Your explanations in conjunction with John's are helping me make sense of 
this. I've been struggling with how to model a many-to-many relationship. It 
seems that I need to break it down so that each table only contains data 
relating to the entity in question. The whole normalizing concept correct?

I'll change my design based on the feedback you and John gave. Thanks for 
the help.

Tony


"Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message 
news:3C68F8F8-B856-4FE6-925D-AAD105D562A5@microsoft.com...
> Tony:
>
> Einstein once said, when asked about explaining the theory of general
> relativity, that you can make things as simple as possible, but no more 
> so.
> So here goes.
>
> Firstly your GamesTable is not well structured.  Having separate columns 
> for
> each team, score and points is what’s known in the jargon as ‘encoding 
> data
> as column headings’, whereas in a relational database data should only be
> stored as values at column positions in rows in tables; this is known as 
> the
> ‘information principle’.  So the table should be decomposed into a Games
> table with columns GameID, GameDate, Venue etc, and a GameTeams table with
> columns GameID (a foreign key in this case) TeamID, Score, Points and any
> others you need such as one to indicate if its the Home or Away team. 
> The
> primary key of this table would be a composite one made up of the GameID 
> and
> TeamID columns.  The table in fact models the many-to-many relationship 
> type
> between Teams and Games.
>
> With the points in one column in a table you can then easily sum the 
> points
> per team with:
>
> SELECT Team, SUM(Points) AS TotalPoints
> FROM GameTeams INNER JOIN Teams
> ON GameTeams.TeamID = Teams.TeamID
> GROUP BY Team;
>
> To show only the team(s) with the highest number of points you can sort 
> the
> results in descending order and return the TOP 1 row(s):
>
> SELECT TOP 1 Team, SUM(Points) AS TotalPoints
> FROM GameTeams INNER JOIN Teams
> ON GameTeams. TeamID = Teams.TeamID
> GROUP BY Team
> ORDER BY SUM(Points) DESC;
>
> You can of course include any other columns from the Teams table in the
> query so long as you include them in its GROUP BY Clause as well as in the
> SELECT clause.
>
> Ken Sheridan
> Stafford, England
>

  I believe your understanding is correct.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Tony wrote:
> Hi John,
> 
> One more question here. I'm trying to understand your design and the 
> relationship between Teams, Games, GameScores.
> 
> Based on your design below, I am guessing the following:
> 
> Teams (Table)        Games (Table)            GameScores (Table)
> TeamID                  TeamID                      TeamID
> TeamName             GameNumber             GameNumber
>                               GameDate                  Score
>                                                                 Points
> 
> Games & GameScores have a relationship between TeamID in Teams Table. 
> Each Team has many games and game scores.
> Games and GameScores have a relationship between GameNumber. Each game 
> having many game scores.
> 
> Is this correct?
> 
> Thanks,
> 
> Tony
> 
> "John Spencer" <spencer@chpdm.umbc> wrote in message 
> news:e9V$rnpOIHA.3400@TK2MSFTNGP03.phx.gbl...
>>
>>
>> SELECT TeamName, Sum(Games.Team1Points) as Score
>> FROM GAMES INNER JOIN TeamTable
>> ON Games.Team1Id = TeamsTable.TeamID
>> GROUP BY TeamName
>>
>> HAVING Sum(Team1Points) =
>>
>>    (SELECT Max(A.SumPoints)
>>    FROM
>>       (SELECT Sum(Games.Team1Points)SumPoints
>>       FROM Games
>>       GROUP BY Games.Team1ID) as A)
>>
>>
>> A real problem here is that your table structure for GamesTable makes 
>> it difficult to get all the points for any one team - since a team 
>> could be referenced as either team1 or team2 and the relevant points 
>> could be stored in team1Score or Team2Score.
>>
>> You could use a UNION query to normalize the date
>> SELECT Team1Id, Team1Score, Team1Points
>> FROM GamesTable
>> UNION ALL
>> SELECT Team2ID, Team2Score, Team2Points
>> FROM GamesTable
>>
>> And substitute the Union query for the games table in the above query. 
>> A better structure might look like
>>
>> Teams (Table)
>> TeamID
>> TeamName
>>
>> Games (Table)
>> GameNumber
>> TeamID
>> GameDate
>> ...
>>
>> GameScores (Table)
>> GameNumber
>> TeamID
>> Score
>> Points
>>
>> Then the query you want would be
>>
>> SELECT TOP 1 Teams.TeamName
>> , Sum(GameScores.Points)
>> FROM Teams INNER JOIN GameScores
>> ON Teams.TeamID = GameScores.TeamID
>> Group By Teams.TeamName
>> ORDER BY Sum(GameScores.Points) Desc
>>
>>
>> '====================================================
>>  John Spencer
>>  Access MVP 2002-2005, 2007
>>  Center for Health Program Development and Management
>>  University of Maryland Baltimore County
>> '====================================================
> 
> 

If you get a prompt for the fieldname, then the field name is probably a 
typo.  Check the spellling of the field name.  Perhaps I screwed up and 
referred to a field that does not exist in the table I referred to or 
the table name is misstyped.


'====================================================
  John Spencer
  Access MVP 2002-2005, 2007
  Center for Health Program Development and Management
  University of Maryland Baltimore County
'====================================================


Tony wrote:
> Hi John,
> 
> Thanks for your help here as well. You nailed it on my problem with 
> having a team referenced as either team1 or team2 and their points could 
> be stored in either team1Points or team2Points. So per your suggestions, 
> and also Ken's, I'm in the process of restructuring my DB to work in 
> manner more inline with proper relational DB design.
> 
> When attempting to run this, I am prompted with a dialog asking for the 
> Teams.TeamID. If I enter 1, I get every TeamName and the score 7 for 
> each (this is the max sum from data I have entered). Why is every team 
> listed? If I alter it to the following, I still get the dialog prompt, 
> but only for the team I'm interested in.
> 
> SELECT TOP 1 TeamName, Sum(Games.Team1Points) as Score
> FROM GAMES INNER JOIN Teams
> ON Games.Team1Id = Teams.TeamID
> GROUP BY TeamName
> 
> HAVING Sum(Team1Points) =
> 
>    (SELECT Max(A.SumPoints)
>    FROM
>       (SELECT Sum(Games.Team1Points) AS SumPoints
>       FROM Games
>       GROUP BY Games.Team1ID) as A);
> 
> Thanks again for your help. It's nice to understand why SQL works the 
> way it does. I'm finally starting to see how this subquery stuff works. 
> The alias naming thing is a bit confusing reading the book (Modern 
> Database Mgmt. By Jeffrey A. Hoffer, Prescott, McFadden). The book 
> states how it's just a way to rename the column heading. Coming from a 
> "intermediate" level 'C' programming background, I see though that you 
> have used this alias to reference the collection. I've seen this before 
> (namely for table names), and the way I relate to it is establishing a 
> variable. E.g. int someNumber; Is this a properly analogy?
> 
> Tony
> 
> 
> "John Spencer" <spencer@chpdm.umbc> wrote in message 
> news:e9V$rnpOIHA.3400@TK2MSFTNGP03.phx.gbl...
>>
>>
>> SELECT TeamName, Sum(Games.Team1Points) as Score
>> FROM GAMES INNER JOIN TeamTable
>> ON Games.Team1Id = TeamsTable.TeamID
>> GROUP BY TeamName
>>
>> HAVING Sum(Team1Points) =
>>
>>    (SELECT Max(A.SumPoints)
>>    FROM
>>       (SELECT Sum(Games.Team1Points)SumPoints
>>       FROM Games
>>       GROUP BY Games.Team1ID) as A)
>>
>>
>> A real problem here is that your table structure for GamesTable makes 
>> it difficult to get all the points for any one team - since a team 
>> could be referenced as either team1 or team2 and the relevant points 
>> could be stored in team1Score or Team2Score.
>>
>> You could use a UNION query to normalize the date
>> SELECT Team1Id, Team1Score, Team1Points
>> FROM GamesTable
>> UNION ALL
>> SELECT Team2ID, Team2Score, Team2Points
>> FROM GamesTable
>>
>> And substitute the Union query for the games table in the above query. 
>> A better structure might look like
>>
>> Teams (Table)
>> TeamID
>> TeamName
>>
>> Games (Table)
>> GameNumber
>> TeamID
>> GameDate
>> ...
>>
>> GameScores (Table)
>> GameNumber
>> TeamID
>> Score
>> Points
>>
>> Then the query you want would be
>>
>> SELECT TOP 1 Teams.TeamName
>> , Sum(GameScores.Points)
>> FROM Teams INNER JOIN GameScores
>> ON Teams.TeamID = GameScores.TeamID
>> Group By Teams.TeamName
>> ORDER BY Sum(GameScores.Points) Desc
>>
>>
>> '====================================================
>>  John Spencer
>>  Access MVP 2002-2005, 2007
>>  Center for Health Program Development and Management
>>  University of Maryland Baltimore County
>> '====================================================
>>
> 
> 

Thank you John for your help. I'm actually accomplishing something now!

Tony

"John Spencer" <spencer@chpdm.umbc> wrote in message 
news:eoLBoctOIHA.484@TK2MSFTNGP06.phx.gbl...
>  I believe your understanding is correct.
>
> '====================================================
>  John Spencer
>  Access MVP 2002-2005, 2007
>  Center for Health Program Development and Management
>  University of Maryland Baltimore County
> '====================================================
>
>
> Tony wrote:
>> Hi John,
>>
>> One more question here. I'm trying to understand your design and the 
>> relationship between Teams, Games, GameScores.
>>
>> Based on your design below, I am guessing the following:
>>
>> Teams (Table)        Games (Table)            GameScores (Table)
>> TeamID                  TeamID                      TeamID
>> TeamName             GameNumber             GameNumber
>>                               GameDate                  Score
>>                                                                 Points
>>
>> Games & GameScores have a relationship between TeamID in Teams Table. 
>> Each Team has many games and game scores.
>> Games and GameScores have a relationship between GameNumber. Each game 
>> having many game scores.
>>
>> Is this correct?
>>
>> Thanks,
>>
>> Tony
>>
>> "John Spencer" <spencer@chpdm.umbc> wrote in message 
>> news:e9V$rnpOIHA.3400@TK2MSFTNGP03.phx.gbl...
>>>
>>>