Ranking Groups in Access

[alsamren]

Hi All,

I have a table with the following fields:

IAKey (Unique key per individual)
HouseholdID (Unique key per customer for each IA)
Assets (per customer) $

I'm trying to pull the top 10 assets (in terms of value) per IAkey. Is there a query that pulls this?

Thanks

 

[Peter Reid]

It would be much easier to do this in a report but as you asked ...

It's not nice but save the following (replacing tbl_YOURTABLE with your table name) as a query called "qry_Rank" before opening it;

SELECT IAKey, HouseholdID, Assets, DCount("[IAKey]","qry_Rank","[IAKey]='" & [IAKey] & "' AND [Assets]>=" & [Assets]) AS RankID
FROM tbl_YOURTABLE
WHERE Assets>0 AND DCount("[IAKey]","qry_Rank","[IAKey]='" & [IAKey] & "' AND [Assets]>=" & [Assets]) <=10
ORDER BY IAKey, Assets DESC;

It there are 2 records with the same IAKey and Asset values then the rank doesn't work correctly