Another Top by Group query question...

[straybullet]

I've been digging through search results and think this should work... but it doesn't.

SELECT A.ID, A.First_Name, A.Last_Name, A.Address_1, A.Address_2, A.Address_3, A.City, A.State, A.Zip, A.Phone, A.Terr_Name, A.[PRS Mkt Vol Decile], A.Special, A.HomeCareRepID, A.ManagerID
FROM qryCombPhysicianRepData AS A
WHERE A.ID In
(SELECT TOP 3 (B.[PRS Mkt Vol Decile]) FROM qryCombPhysicianRepData AS B
WHERE B.HomeCarerepID = A.HomeCarerepID order by B.[PRS Mkt Vol Decile] DESC, B.ID)
ORDER BY A.[PRS Mkt Vol Decile] DESC , A.ID;

Now, the subquery does work on it's own if I substitute a name like so:

SELECT TOP 3 (B.[PRS Mkt Vol Decile]) FROM qryCombPhysicianRepData AS B
WHERE B.HomeCarerepID = "Smith" order by B.[PRS Mkt Vol Decile] DESC, B.ID

But for some reason, I can't get it to show me the top 10 for each HomeCarerep...

Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

please disregard... I had pasted the wrong version above and tried to 'edit' by using the back button, but it just made another post.

Let them hate - so long as they fear... Lucius Accius