Grouping Problem

[Paul W Smith]

I have a table which contains the following fields (which represent
results):

Score, Team, Opponents

I needs a query that will return the highest score for each team AND who the
opponent was in that game.

It is a simple matter to Group by Team and then find the max of Score, this
returns the highest score for each team. However as soon as I add the
Opponent field the max clause no longer works and I just get every result in
the table.

How do I achieve what I need?

 

[Dale Fye]

Paul,

Do you have a date field in the table that indicates the date of the event?
Or better yet, an ID field that is an autonumber field?

What results do you want to return if the Team had the same "high score"
against multiple opponents? Do you want to return all of those records, or
only one of them? If only one, which one?

Dale

 

[Paul W Smith]

I have treid to simplify my problem, but it seems to get an answer I need to
get the detail correct.

I have a table which represents a performance. It has various fields but
the ones I am interested in are Score, Team and opponents. It has a field
which is PairingID.

This Pairing ID comes from a Pairing table which has two records for every
game, one for each team.

Every record in the Pairing table has a field which refers to a Game table
which obviously conatins details of the game, date, venue etc.

Examples

Result Table
PairingID Team Score Opponent
1 Team A 100 Team Z
2 Team Z 98 Team A
3 Team B 96 Team Y
4 Team Y 99 Team B


Pairing Table
PairingID FixtureID Team
1 1 Team A
2 1 Team Z
3 2 Team B
4 2 Team Y

Game Table
GameID WeekID DivisionID GroundID Played
Start Time
1 1 1 1
True 11.30am
2 1 1 2
True 11.30am

The result table did not have any reference to opponent, because I have
never needed to know who the opponent was for any other DB purpose.

I used the Game table to generate the schedules because each game has two
teams etc.

I do not have a primary key in the Results table, because I have never
needed one, I guess I could add one.

http://www.doublesix.plus.com/Access/Relationships.gif

I hope the above relationship diagram helps.

 

[Paul W Smith]

To answer your questions:

No date field - see other post.

Could add autonumber ID if necessary.

If multi - highscores just return first.

 

[John Spencer]

SELECT Score, Team, Opponents
FROM [Your Table] as T
WHERE T.Score =
(SELECT Max(T1.Score)
FROM [Your Table] as T1
WHERE T1.Team= T.Team)

That will give you multiple records if the Max Score for any Team has ties.
If you don't want "ties", then you can try

SELECT Score, Team, First(Opponents) as TheOpponent
FROM [Your Table] as T
WHERE T.Score =
(SELECT Max(T1.Score)
FROM [Your Table] as T1
WHERE T1.Team= T.Team)
GROUP BY Score, Team

 

[Paul W Smith]

Both the below queries work fine - thank you.

However my issue is now further complicated. For reasons relating to my
application I have to display the high score as a concatenated text field.
The actual score is part of that but there are some game specific
qualifiers.

In the table you refer to as [MyTable] I have two additional fields which
are used, one is 'Wkts' and the other is 'Dec' (which is true of false).

What I actually need to display can be created in MyTable using:

ScoreText: [MyTable]!Score &
IIf([MyTable]!Wkts>9,"",IIf([MyTable]!Dec=True,"-" & [MyTable]!Wkts & "
dec","-" & [MyTable]!Wkts))

e.g. Score = 270, wkts = 4, and Dec = True gives "270-4 dec"

or Score = 125, wkts = 9, and Dec = False gives "125-9"

I had hoped to just substitute ScoreText for Score in your query, but
obviously this does not work, although I am not sure why.

SELECT ClubName, ScoreText, First(Opponents) As OppoName
FROM [MyTable] AS T
WHERE T.ScoreText = SELECT Max(T1.ScoreText) FROM [MyTable] as T1 WHERE
T1.ClubName = T.ClubName
GROUP BY ClubName, ScoreText

I have made sure that the order is correct in [MyTable] by doing the
appropriate sorting on Score and Wkts Columns. The highest score is the
first record for each team as it is encountered.

This works for eight of the ten teams in my test data, but not two.

SELECT Score, Team, Opponents
FROM [Your Table] as T
WHERE T.Score =
(SELECT Max(T1.Score)
FROM [Your Table] as T1
WHERE T1.Team= T.Team)

That will give you multiple records if the Max Score for any Team has
ties. If you don't want "ties", then you can try

SELECT Score, Team, First(Opponents) as TheOpponent
FROM [Your Table] as T
WHERE T.Score =
(SELECT Max(T1.Score)
FROM [Your Table] as T1
WHERE T1.Team= T.Team)
GROUP BY Score, Team

I have a table which contains the following fields (which represent
results):

Score, Team, Opponents

I needs a query that will return the highest score for each team AND who
the opponent was in that game.

It is a simple matter to Group by Team and then find the max of Score,
this returns the highest score for each team. However as soon as I add
the Opponent field the max clause no longer works and I just get every
result in the table.

How do I achieve what I need?

 

[John Spencer]

Try something like the following. You can have SCORE and SCORETEXT both in
your "MyTable".

SELECT ClubName, ScoreText, First(Opponents) As OppoName
FROM [MyTable] AS T
WHERE T.Score = (SELECT Max(T1.Score) FROM [MyTable] as T1 WHERE
T1.ClubName = T.ClubName)
GROUP BY ClubName, ScoreText

Both the below queries work fine - thank you.

However my issue is now further complicated. For reasons relating to my
application I have to display the high score as a concatenated text field.
The actual score is part of that but there are some game specific
qualifiers.

In the table you refer to as [MyTable] I have two additional fields which
are used, one is 'Wkts' and the other is 'Dec' (which is true of false).

What I actually need to display can be created in MyTable using:

ScoreText: [MyTable]!Score &
IIf([MyTable]!Wkts>9,"",IIf([MyTable]!Dec=True,"-" & [MyTable]!Wkts & "
dec","-" & [MyTable]!Wkts))

e.g. Score = 270, wkts = 4, and Dec = True gives "270-4 dec"

or Score = 125, wkts = 9, and Dec = False gives "125-9"

I had hoped to just substitute ScoreText for Score in your query, but
obviously this does not work, although I am not sure why.

SELECT ClubName, ScoreText, First(Opponents) As OppoName
FROM [MyTable] AS T
WHERE T.ScoreText = SELECT Max(T1.ScoreText) FROM [MyTable] as T1 WHERE
T1.ClubName = T.ClubName
GROUP BY ClubName, ScoreText

I have made sure that the order is correct in [MyTable] by doing the
appropriate sorting on Score and Wkts Columns. The highest score is the
first record for each team as it is encountered.

This works for eight of the ten teams in my test data, but not two.

SELECT Score, Team, Opponents
FROM [Your Table] as T
WHERE T.Score =
(SELECT Max(T1.Score)
FROM [Your Table] as T1
WHERE T1.Team= T.Team)

That will give you multiple records if the Max Score for any Team has
ties. If you don't want "ties", then you can try

SELECT Score, Team, First(Opponents) as TheOpponent
FROM [Your Table] as T
WHERE T.Score =
(SELECT Max(T1.Score)
FROM [Your Table] as T1
WHERE T1.Team= T.Team)
GROUP BY Score, Team

I have a table which contains the following fields (which represent
results):

Score, Team, Opponents

I needs a query that will return the highest score for each team AND who
the opponent was in that game.

It is a simple matter to Group by Team and then find the max of Score,
this returns the highest score for each team. However as soon as I add
the Opponent field the max clause no longer works and I just get every
result in the table.

How do I achieve what I need?

 

[Paul W Smith]

John,

Did you look at the web reference to my DB relationships?

Can you see a better way of finding the highest scores?

I am using your last query, which is being run on another query :

SELECT NewtUpdates.Score, NewtUpdates.Wkts, NewtUpdates.Dec,
tClubs.ClubName, tClubs_1.ClubName AS Oppo, [NewtUpdates]![Score] &
IIf([NewtUpdates]![Wkts]>9,"",IIf([NewtUpdates]![Dec]=True,"-" &
[NewtUpdates]![Wkts] & " dec","-" & [NewtUpdates]![Wkts])) AS ScoreText,
tDivisionTeams.Year, tDivisions.Team
FROM tClubs AS tClubs_1 INNER JOIN ((tClubs INNER JOIN (tDivisionTeams INNER
JOIN (NewtUpdates INNER JOIN tPairings ON NewtUpdates.PairingID =
tPairings.PairingID) ON tDivisionTeams.TeamID = tPairings.TeamID) ON
tClubs.ClubID = tDivisionTeams.ClubID) INNER JOIN (tDivisions INNER JOIN
(tPairings AS tPairings_1 INNER JOIN tDivisionTeams AS tDivisionTeams_1 ON
tPairings_1.TeamID = tDivisionTeams_1.TeamID) ON tDivisions.DivisionID =
tDivisionTeams_1.DivisionID) ON (tDivisions.DivisionID =
tDivisionTeams.DivisionID) AND (NewtUpdates.OppoPairID =
tPairings_1.PairingID)) ON tClubs_1.ClubID = tDivisionTeams_1.ClubID
WHERE (((NewtUpdates.Score)>0) AND ((tDivisions.Team)=1))
ORDER BY NewtUpdates.Score DESC , NewtUpdates.Wkts;

It's easier to see graphically
http://www.doublesix.plus.com/Access/Query2.gif

I would like to do it in one query and then use another to update tClubs
with this data.

I apreciate any comments you may like to make, although I am very committed
to my data structure.

PWS

Try something like the following. You can have SCORE and SCORETEXT both
in your "MyTable".

SELECT ClubName, ScoreText, First(Opponents) As OppoName
FROM [MyTable] AS T
WHERE T.Score = (SELECT Max(T1.Score) FROM [MyTable] as T1 WHERE
T1.ClubName = T.ClubName)
GROUP BY ClubName, ScoreText

Both the below queries work fine - thank you.

However my issue is now further complicated. For reasons relating to my
application I have to display the high score as a concatenated text
field. The actual score is part of that but there are some game specific
qualifiers.

In the table you refer to as [MyTable] I have two additional fields which
are used, one is 'Wkts' and the other is 'Dec' (which is true of false).

What I actually need to display can be created in MyTable using:

ScoreText: [MyTable]!Score &
IIf([MyTable]!Wkts>9,"",IIf([MyTable]!Dec=True,"-" & [MyTable]!Wkts & "
dec","-" & [MyTable]!Wkts))

e.g. Score = 270, wkts = 4, and Dec = True gives "270-4 dec"

or Score = 125, wkts = 9, and Dec = False gives "125-9"

I had hoped to just substitute ScoreText for Score in your query, but
obviously this does not work, although I am not sure why.

SELECT ClubName, ScoreText, First(Opponents) As OppoName
FROM [MyTable] AS T
WHERE T.ScoreText = SELECT Max(T1.ScoreText) FROM [MyTable] as T1 WHERE
T1.ClubName = T.ClubName
GROUP BY ClubName, ScoreText

I have made sure that the order is correct in [MyTable] by doing the
appropriate sorting on Score and Wkts Columns. The highest score is the
first record for each team as it is encountered.

This works for eight of the ten teams in my test data, but not two.

SELECT Score, Team, Opponents
FROM [Your Table] as T
WHERE T.Score =
(SELECT Max(T1.Score)
FROM [Your Table] as T1
WHERE T1.Team= T.Team)

That will give you multiple records if the Max Score for any Team has
ties. If you don't want "ties", then you can try

SELECT Score, Team, First(Opponents) as TheOpponent
FROM [Your Table] as T
WHERE T.Score =
(SELECT Max(T1.Score)
FROM [Your Table] as T1
WHERE T1.Team= T.Team)
GROUP BY Score, Team


I have a table which contains the following fields (which represent
results):

Score, Team, Opponents

I needs a query that will return the highest score for each team AND
who the opponent was in that game.

It is a simple matter to Group by Team and then find the max of Score,
this returns the highest score for each team. However as soon as I add
the Opponent field the max clause no longer works and I just get every
result in the table.

How do I achieve what I need?