Odd query - cant figure this out! (1 Viewer)

[mugman17]

Hi there:

I have a table thats set up like this:

PlayerID
Season
TeamID
PassingYards
RushingYards
ReceivingYards

I would like the query to find the highest number in Passing, Rushing and Receiving per season. Do I need to do each one in a seperate query?

Thanks.

 

[boblarson]

Are you looking for the PLAYER with the highest total or just the numbers for the highest total?

 

[boblarson]

For just getting the highest numbers in each category for the season:

SELECT tblPlayersSeasons.Season, Max(tblPlayersSeasons.PassingYards) AS MostPassing, Max(tblPlayersSeasons.RushingYards) AS MostRushing, Max(tblPlayersSeasons.ReceivingYards) AS MostReceiving
FROM tblPlayersSeasons
GROUP BY tblPlayersSeasons.Season;


For the most per team:
SELECT tblPlayersSeasons.Season, tblPlayersSeasons.TeamID, Max(tblPlayersSeasons.PassingYards) AS MostPassing, Max(tblPlayersSeasons.RushingYards) AS MostRushing, Max(tblPlayersSeasons.ReceivingYards) AS MostReceiving
FROM tblPlayersSeasons
GROUP BY tblPlayersSeasons.Season, tblPlayersSeasons.TeamID;

 

[mugman17]

Yes, sorry, I need the player per season with the highest total for each.

 

[MSAccessRookie]

Hi there:

I have a table thats set up like this:

PlayerID
Season
TeamID
PassingYards
RushingYards
ReceivingYards

I would like the query to find the highest number in Passing, Rushing and Receiving per season. Do I need to do each one in a seperate query?

Thanks.

Not exactly sure if I understand your needs completely, but I believe that a UNION Query similar to the following (UNTESTED CODE) might provide what you are seeking.

[FONT=Courier New]SELECT "Max Passing Yards:   " As theStat, PlayerID, Season, TeamID, Max(PassingYards)[/FONT]
[FONT=Courier New]FROM YourTable[/FONT]
[FONT=Courier New]GROUP BY PlayerID, Season, TeamID [/FONT]
[FONT=Courier New]UNION[/FONT]
[FONT=Courier New]SELECT "Max Rushing Yards:   " As theStat, PlayerID, Season, TeamID, Max(RushingYards)[/FONT]
[FONT=Courier New]FROM YourTable[/FONT]
[FONT=Courier New]GROUP BY PlayerID, Season, TeamID [/FONT]
[FONT=Courier New]UNION[/FONT]
[FONT=Courier New]SELECT "Max Receiving Yards: " As theStat, PlayerID, Season, TeamID, Max(ReceivingYards)[/FONT]
[FONT=Courier New]FROM YourTable[/FONT]
[FONT=Courier New]GROUP BY PlayerID, Season, TeamID [/FONT]
[FONT=Courier New]ORDER BY PlayerID[/FONT]

 

[boblarson]

This also should do it (and tested):

SELECT TOP 1 tblPlayersSeasons.Season, tblPlayersSeasons.PlayerID, Max(tblPlayersSeasons.PassingYards) AS Yards, "Passing" As dType
FROM tblPlayersSeasons
GROUP BY tblPlayersSeasons.Season, tblPlayersSeasons.PlayerID
ORDER BY Max(tblPlayersSeasons.PassingYards) DESC
UNION
SELECT TOP 1 tblPlayersSeasons.Season, tblPlayersSeasons.PlayerID,  Max(tblPlayersSeasons.RushingYards) AS Yards, "Rushing" As dType
FROM tblPlayersSeasons
GROUP BY tblPlayersSeasons.Season, tblPlayersSeasons.PlayerID
UNION
SELECT TOP 1 tblPlayersSeasons.Season, tblPlayersSeasons.PlayerID,   Max(tblPlayersSeasons.ReceivingYards) AS Yards, "Receiving" As dType
FROM tblPlayersSeasons
GROUP BY tblPlayersSeasons.Season, tblPlayersSeasons.PlayerID;

 

[mugman17]

That appears to be giving me the highest number and the season it was accomplished, but its not breaking it down per season. What I need is somewhat like this below:

Season - 2009; PlayerID - 1; PassingYards - 3000
Season - 2008; PlayerID - 1; PassingYards - 3400
etc.

This works well:

SELECT tblSeasons.Season, Max(tblSeasons.PassingYards) AS Yards, "Passing" AS dType
FROM tblSeasons
WHERE (((tblSeasons.TeamID)=1))
GROUP BY tblSeasons.Season
ORDER BY Max(tblSeasons.PassingYards) DESC;

But when I add the PlayerID to the query, I wind up getting a total for every player on the team for each season.

 

[boblarson]

How about this:

SELECT tblSeasons.Season, Max(tblSeasons.PassingYards) AS Yards, "Passing" AS dType
FROM tblSeasons
WHERE (((tblSeasons.TeamID)=1))
GROUP BY tblSeasons.Season
ORDER BY Max(tblSeasons.PassingYards) DESC
UNION
SELECT tblSeasons.Season, Max(tblSeasons.RushingYards) AS Yards, "Rushing" AS dType
FROM tblSeasons
WHERE (((tblSeasons.TeamID)=1))
GROUP BY tblSeasons.Season
UNION
SELECT tblSeasons.Season, Max(tblSeasons.ReceivingYards) AS Yards, "Receiving" AS dType
FROM tblSeasons
WHERE (((tblSeasons.TeamID)=1))
GROUP BY tblSeasons.Season;

 

[mugman17]

That gives me the totals and breaks it down by year, but I still need to have a PlayerID attached to each one. When I try that, everything gets messed up.

 

[boblarson]

Okay, I got it but it took a lot more than I originally anticipated. It took me a few queries to get it. See the final one (the Union query) in the sample below and the others are just steps that are necessary (at least in my example) to make it work. You can just backtrack from the Union query to see what is needed, etc.

 

[mugman17]

That worked! Thanks Bob. I really appreciate your help.

 

[boblarson]

That worked! Thanks Bob. I really appreciate your help.

No problem, that one was interesting to work on, for sure. And there is likely a better solution and someone may come up with that as well.

 

[mugman17]

Still having issues with it. If two players have the same yards in the same year (which happened believe it or not), it puts the totals for that player into both teams.

Its because of the joining of the passing yards in the TopPassing query, same goes for the other queries.

 

[boblarson]

Still having issues with it. If two players have the same yards in the same year (which happened believe it or not), it puts the totals for that player into both teams.

Its because of the joining of the passing yards in the TopPassing query, same goes for the other queries.

Yes, if there are ties then both, or three, etc. would show up. I don't know that there is any way to avoid that. I thought though that you were looking for the OVERALL leaders (which if a tie, both would be) and not PER TEAM.

 

[mugman17]

I have them so they are split up per team. But now I am having players showing up in the team listing even if they dont play on that team, if they have the same yard totals. So if two guys had 4500 yards passing in 2009, one was with Team 18 and the other was with Team 51. They each appear as both Team 18 and 51.