Here's the query...

SELECT tblSJHSVendorCount.MembID,
tblSJHSVendorCount.Vendor,
(SELECT TOP 1 VendCount2.CountOfVendor
FROM tblSJHSVendorCount AS VendCount2
WHERE tblSJHSVendorCount.MembID = VendCount2.MembID
ORDER BY VendCount2.CountOfVendor DESC, Counter) AS MaxVendCount
FROM tblSJHSVendorCount;
Each MembID as multiple vendors. Each vendor has a count associated with is. [Counter] is a unique id on the table. I want the MembID and the Vendor with the highest count. One row per member. It is not working. The table has 1470 rows and the query returns all rows.

Help!

Greg

Try:
SELECT T1.MembID, T1.Vendor, T1.CountOfVendor
FROM tblSJHSVendorCount T1
WHERE T1.Vendor =
(SELECT TOP 1 T2.Vendor
FROM tblSJHSVendorCount T2
WHERE T2.MembID = T1.MembID
ORDER BY T2.CountOfVendor DESC);

Thanks, that got it. At least it was very close. I did need to add back in the [Counter] to the subquery because two of the members have two rows where both vendor counts were identical. The unique counter breaks the tie.

I see now what I was doing wrong. This was very helpful. Thanks.

Excellent. I'm glad you got it sorted out :)