Query with no doubles

[Chris]

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???

[Allen Browne]

Use a subquery to get the id associated with [MaxOfGames Played].

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

If subqueries are new, see:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris" <ch*********@gmail.comwrote in message
news:11**********************@s48g2000cws.googlegr oups.com...

>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???