Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
From: Philipp Post <post.philipp@googlemail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Reducing to One Query
Date: Fri, 17 Aug 2012 06:23:01 -0700 (PDT)
Organization: http://groups.google.com
Lines: 14
Message-ID: <9c01429a-eafd-4600-8dcf-b8b74f40036d@googlegroups.com>
References: <r8nq28hte3lhrka9pviujmo1apmopg8ult@4ax.com>
NNTP-Posting-Host: 194.9.141.19
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1345209891 20418 127.0.0.1 (17 Aug 2012 13:24:51 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 17 Aug 2012 13:24:51 +0000 (UTC)
In-Reply-To: <r8nq28hte3lhrka9pviujmo1apmopg8ult@4ax.com>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=194.9.141.19; posting-account=55hwJgoAAABhSU2zeOH2OUPhz-zWMfy6
User-Agent: G2/1.0
Xref: csiph.com comp.databases.ms-sqlserver:1218

If this should work in MS Access you will end up with something like that - same as your query without the temp table.

SELECT S.Team, S.PlayerName
FROM Stats AS S
INNER JOIN
  (SELECT Max(pct) As max_pct, Team
   FROM Stats AS S
   GROUP BY Team) AS T1 
ON S.pct = T1.max_pct
  AND S.Team = T1.Team
;

brgds
Philipp Post