Max value based on criteria in three different tables

[graympa]

Brand new to Access; am trying to learn by doing. I am interested as much in learning the best methods as I am the actual answer.



I am trying to display the starting quarterback for teams in the NFL, based on pass attempts. I have a table of teams, of players, and of performance (week and pass attempts). What I want to do is show, for each team and week, who threw the most passes that week. I think I should group the quarterbacks by team, and then figure who had the most passes for each week in that group. Do I do this all at once (how?), or create a query (to get team / QBs) and then query from that?

Thanks.

[RayMilhon]

So do you have 1 table with those data elements or 3 tables?

The data structure you're using will tell how to build the queries you want.

[graympa]

Sorry wasn't clearer. 3 tables: Teams (Team_Name), Players(Position,Team), and Stats(Player_ID,Pass_Attempts).

[graympa]

Stats also has Week.

[JamesDeckert]

Seems like you could do it all at once. Add all relevant tables to query, group by team, then on the total line do MAX for number of passes. only put team, quarterback and numpasses as your fields to see if its working right. Then you can add other fields if you need to.

[RayMilhon]

I'm going to make some assumptions here.

Team
TeamID
TeamName

Players
PlayerID
PlayerName
PlayerPosition
TeamID

Stats
StatsID
PlayerID
Gamedate
PassesAttempted
PassesCompleted

Your Query would then look like this

Select Team.TeamName, Player.PlayerName, stats.gamedate, stats.PassesAttempted
From Stats Inner join Player on stats.PlayerID = Player.PlayerID Inner Join Team on Player.TeamID = Team.TeamID
Where GameDate between [Startdate] and [enddate]
Order By stats.gamedate, stats.PassesAttempted desc

With the data structure I provided you could also query on most PassesCompleted by week or Highest Completion Percentage by week.

[graympa]

Okay, that worked. My problem was I didn't set up my relationships correctly, and I didn't do the inner joins correctly . I realized I can do it design view and then go back and see how SQL does it.