Avatar of jdhackett
Flag for Ireland

asked on 

Top N Sales Per Profession for Access Report

I've looked at  Allen Brownes sub query 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.
SELECT ProfPrem.Profession, ProfPrem.PolRef, ProfPrem.Ann_premium
FROM ProfPrem
WHERE (((ProfPrem.PolRef) In (SELECT TOP 5 PolRef                            
   FROM ProfPrem AS Dupe                              
   WHERE Dupe.PolRef = ProfPrem.PolRef   ORDER BY Dupe.Ann_Premium DESC)))
ORDER BY ProfPrem.Profession;

Open in new window

However this is showing me all policies, not the top 5. Any idea what I'm doing wrong?
Microsoft AccessDatabases

Avatar of undefined
Last Comment

8/22/2022 - Mon