<div>
Try this. I just removed the unnecessary braces.<br />

<pre><code class="code-1 nolinks" id="code-20-41374638-1"><span>SELECT ProfPrem.Profession, ProfPrem.PolRef, ProfPrem.Ann_premium</span>
<span>FROM ProfPrem</span>
<span>WHERE ProfPrem.PolRef In (SELECT TOP 5 PolRef                            </span>
<span>   FROM ProfPrem AS Dupe                              </span>
<span>   WHERE Dupe.PolRef = ProfPrem.PolRef   ORDER BY Dupe.Ann_Premium DESC)</span>
<span>ORDER BY ProfPrem.Profession;</span>
</code></pre>
</div>


<div>
Make sure you that the PolRef field has Unique values.
</div>


<div>
PolRef is the primary key and is unique.
</div>


<div>
And the SQL you posted doesn't work either. <br />
Btw, why would PolRef have to unique?
</div>


<div>
To clarify - I mean it it still returns the same result - all rows.
</div>


<div>
the query posted by both should work...<br />
can you upload a sample db with your table (obfuscate sensitive information)
</div>


<div>
I would think that your in clause should use the table alias:<br />
<br />
In (SELECT TOP 5 <b>Dupe</b>.PolRef &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br />
&nbsp; &nbsp;FROM ProfPrem AS Dupe
</div>


<div>
Thanks guys for your suggestions so far.<br />
I've attached a sample which just has the query and a table with some demo records. <br />
You can see here that the query returns all records, where I just want the top 5 per profession.<br />
<a href="https://filedb.experts-exchange.com/incoming/2015/12_w51/1056124/Sample.accdb" target="_blank" class="file-inline" title="Sample.accdb (472 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Sample.accdb</a>
</div>


Sample.accdb

<div>
try this query<br />
<br />
SELECT ProfPrem.Profession, ProfPrem.PolRef, ProfPrem.Ann_premium<br />
FROM ProfPrem Inner Join<br />
(SELECT TOP 5 ProfPrem.PolRef<br />
FROM ProfPrem<br />
ORDER BY ProfPrem.Ann_premium DESC) As P<br />
On ProfPrem.PolRef=P.PolRef
</div>


<div>
or simply this query<br />
<br />
SELECT TOP 5 ProfPrem.PolRef, ProfPrem.Profession, ProfPrem.Ann_premium<br />
FROM ProfPrem<br />
ORDER BY ProfPrem.Ann_premium DESC , ProfPrem.Profession;
</div>


<div>
Rey - those queries didn't work for me. Each just had 5 rows. <br />
I want 5 rows per profession.<br />
I'm using Access 2010, did those queries work for you in a different version of Access?
</div>


<div>
The results should look like<br />
<br />
PolRef &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Profession &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Ann_premium<br />
PolicyNo31 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Accountant &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10000<br />
PolicyNo07 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Accountant &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9000<br />
PolicyNo29 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Accountant &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8000<br />
PolicyNo05 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Accountant &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7000<br />
PolicyNo04 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Accountant &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6000<br />
PolicyNo12 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Architect &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10000<br />
PolicyNo13 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Architect &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9000<br />
PolicyNo26 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Architect &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8000<br />
PolicyNo10 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Architect &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7000<br />
PolicyNo14 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Architect &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6000
</div>


<div>
Above query produces the following results:<br />

<pre><code class="code-10 nolinks" id="code-20-41374902-1"><span>Profession	PolRef	Ann_premium</span>
<span>Accountant	PolicyNo31	10000</span>
<span>Accountant	PolicyNo07	9000</span>
<span>Accountant	PolicyNo29	8000</span>
<span>Accountant	PolicyNo05	7000</span>
<span>Accountant	PolicyNo04	6000</span>
<span>Architect	PolicyNo12	10000</span>
<span>Architect	PolicyNo13	9000</span>
<span>Architect	PolicyNo26	8000</span>
<span>Architect	PolicyNo10	7000</span>
<span>Architect	PolicyNo14	6000</span>
<span>Design and Construct	PolicyNo23	1000</span>
<span>Design and Construct	PolicyNo03	900</span>
<span>Design and Construct	PolicyNo15	800</span>
<span>Design and Construct	PolicyNo33	700</span>
<span>Engineer	PolicyNo34	100</span>
<span>Engineer	PolicyNo25	90</span>
<span>Engineer	PolicyNo32	80</span>
<span>Miscellaneous	PolicyNo40	10000</span>
<span>Miscellaneous	PolicyNo06	9000</span>
<span>Miscellaneous	PolicyNo24	8000</span>
<span>Miscellaneous	PolicyNo35	7000</span>
<span>Miscellaneous	PolicyNo39	6000</span>
<span>Surveyor	PolicyNo30	300</span>
<span>Surveyor	PolicyNo28	200</span>
<span>Technology	PolicyNo20	600</span>
<span>Technology	PolicyNo17	500</span>
</code></pre>
</div>


<div>
Thanks alimark, that worked. <br />
I want to make sure I understand why.<br />
I see you changed the &quot;Dupe&quot; to link on Profession. Should the link field usually be the field I'm grouping on?
</div>


<div>
oops, sorry i didn't see the &quot;per profession&quot; requirement
</div>


<div>
Thanks for the detailed explanation, very helpful.
</div>


<div>
<div class="content wysiwyg-content">
Hi<br />
I've looked at &nbsp;<a href="http://allenbrowne.com/subquery-01.html" rel="ugc">Allen Brownes sub query</a> page, but I'm still confused.<br />
I have a table for call ProfPrem which has 3 fields:<br />

<div class="bullet">
PolRef &nbsp; - Unique policy ref
</div>
<div class="bullet">
Profession &nbsp; - The Profession of the policy holder. 
</div>
<div class="bullet">
Ann_Premium &nbsp;- The annual premium
</div>
<br />
So I'd like to show the top 5 premiums per profession.<br />

<pre><code class="code-1 nolinks" id="code-10-28900968-1"><span>SELECT ProfPrem.Profession, ProfPrem.PolRef, ProfPrem.Ann_premium</span>
<span>FROM ProfPrem</span>
<span>WHERE (((ProfPrem.PolRef) In (SELECT TOP 5 PolRef                            </span>
<span>   FROM ProfPrem AS Dupe                              </span>
<span>   WHERE Dupe.PolRef = ProfPrem.PolRef   ORDER BY Dupe.Ann_Premium DESC)))</span>
<span>ORDER BY ProfPrem.Profession;</span>
</code></pre>
<br />
However this is showing me all policies, not the top 5. Any idea what I'm doing wrong?
</div>
</div>


Hi
I've looked at  Allen Brownes sub query (http://allenbrowne.com/subquery-01.html) page, but I'm still confused.
I have a table for call ProfPrem which has 3 fields:
PolRef   - Unique policy ref
Profession   - The Profession of the policy holder. 
Ann_Premium  - The annual premium

So I'd like to show the top 5 premiums per profession.
(CODE)

However this is showing me all policies, not the top 5. Any idea what I'm doing wrong?

Top N Sales Per Profession for Access Report

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.