Nested query???

[Vicente Rodriguez Eguibar]

Hi all,

I have an Access 2003 database hosting information of a bowling tournament.
This db is accesed by several web pages, retriving user, teams, etc. info.
The tables are BOWLERS (ID_Bowler, FirstName, LastName...), TEAMS (ID_Team,
TeamName, ID_Capitan, ID_Bowler...) DATES (ID_Date, DatePlayed...) and
Bowl_Averages (ID_Bowler, ID_Date, Player_Average).

One of the queries should return FirstName LastName, DatePlayed and highest
Averageor each bowler. I'm able to retrive the names and the highest average
from each bowler:

John Doe - 228
Donald Duck - 185
Bart Simpson - 147

but when I try to include the date when the highest average was achived the
result is an unespected very long list:

John Doe - 10/01/03 -228
John Doe - 15/01/03 - 206
Donald Duck - 10/01/03 -185
Donald Duck - 15/01/03 - 180
Bart Simpson - 15/01/03 - 147
John Doe - 15/01/03 - 140
Donald Duck - 15/01/03 - 126
Bart Simpson - 10/01/03 - 113

I've searched hundred of web pages without success. Can anyone help me wuth
the SQL statement??? or can someone point me to an example I can use???

Thanks in advance for your help.

 

[John Spencer (MVP)]

It would help if you posted your SQL statement that almost works. That way Tom
Ellison can modify it to show you what needs to be done. OR one of us lesser
SQL mortals can take a stab at it.

 

[Vicente Rodriguez Eguibar]

No problem, here it is:

SELECT Bowlers.FirstName, Teams.TeamName, Dates.DatePlayed,
Max(Bowl_Averages .Player_Average) AS MaxOfProm_Jugador
FROM Teams INNER JOIN (Dates INNER JOIN (Bowlers INNER JOIN Player_Average
ON Bowlers.ID_Bowler = Bowl_Averages.ID_Bowler) ON Dates.ID_Date =
Player_Average.ID_Date) ON Teams.ID_Team= Bowlers.ID_Team
GROUP BY Bowlers.FirstName, Teams.TeamName, Dates.DatePlayed
ORDER BY Max(Bowl_Averages.Player_Average) DESC;

As Explained before, this query will return the highest average from several
dates for each bowler, and not the highest of each bowler regarding of the
date.

Thanks in Advance.

Vicente Rodriguez Eguibar
MCSE #31507