By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,641 Members | 2,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,641 IT Pros & Developers. It's quick & easy.

Query with no doubles

P: n/a
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???

Mar 11 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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???
Mar 12 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.