Query with no doubles

[chrislabs]

I have a table which displays playerID, YearID, RosterID, and Games
played. Then I have a query that looks like this

SELECT Positions.playerID, Positions.YearID, Positions.rosterID,
Max(Positions.[Games Played]) AS [MaxOfGames Played]
FROM Positions
GROUP BY Positions.playerID, Positions.YearID, Positions.rosterID;


My problem is that I dont want any doubles so if a player have 2
rosterID's in 1 year I just want to see that roster ID associated with
the MaxofGamesPlayed. How do I do this???

 

[MicroE]

Remove RosterID from your query and save it (i.e. qyrMaxGamesPlayed).

SELECT Positions.playerID, Positions.YearID, Max(Positions.[Games Played]) AS [MaxOfGames Played]
FROM Positions
GROUP BY Positions.playerID, Positions.YearID;

Then create a second query linking the Positions table and qyrMaxGamesPlayed query by fields:
playerID, playerID
YearID, YearID
Games Played, MaxOfGames

Select All from the Positions table.

 

[chrislabs]

It worked!!! Thanks a lot..

 

[MicroE]

You are welcome – glad I could assist.