Restricting Records

[jlo]

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