Restricting Records



I have a PROVIDER field with 600 providers. Each provider has 5 members or
more. I need to pull only five members from each provider. Is there a way to
do that in a query?

For example: For provider # 0001, I only need to see the first 5 records
not all 7 records.

Provider# MemberFName Member Last Name
0001 Jane Do
0001 Mary Smith
0001 Nancy Do
0001 Jay Smith
0001 Daryl Thomas
0001 Tom Jones
0001 Glenn Jones



John Spencer MVP

You can use a correlated subquery to identify the records you want

SELECT P.[Provider#]
, P.MemberFName
, P.[Member Last Name]
FROM [Some Provider Table] as P
WHERE MemberFName & "//" & MemberLName IN
(SELECT TOP 5 MemberFName & "//" & MemberLName
FROM [Some Provider Table] as T
WHERE T.[Provider#] = P.[Provider#]
ORDER BY MemberFName, MemberLname)

OR you can refer to the table twice in the query and use ranking to limit the
output to five records per provider. (This may be faster, but you cannot
update the records returned.)

SELECT A.[Provider#], A.MemberFName, A.MemberLName
FROM ProviderTable As A LEFT JOIN ProviderTable As B
ON A.[Provider#] = B.Provider#
AND A.MemberFName & "//" & A.MemberLName > B.MemberFName & "//" & B.MemberLName
GROUP BY A.[Provider#], A.MemberFName, A.MemberLName
HAVING COUNT B.[Provider#] < 5

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question