Summary query question 1

[jrflanne]

Hi,
Newbie warning!
I have a table with three fields: caseID, custID, and pct_interest. I am looking for a way to find the maximum pct_interest for each caseID and then write caseID, the custID who has the maximum pct_interest and pct_interest for that caseID (GroupBy caseID).

caseID custID pct_interest
1 Joe 25
1 Bob 25
1 Bill 50
2 Sally 75
2 Debbie 25
My desired result:
caseID custID pct_interest
1 Bill 50
2 Sally 75

The problem (besides my total lack of Access experience)is if I GroupBy caseID and custID and Max pct_interest, the resulting table has all of the records in it. I understand why this is, but how the heck can I spit out the cust_ID who has the maximum pct_interest?

I'm sure there is a rather simple answer to this but I am thrashing a bit. Thanks for any assistance.
Jack

 

[PHV]

In the SQL pane of the query window:
SELECT A.caseID, A.custID, A.pct_interest
FROM yourTable AS A INNER JOIN (
SELECT caseID, Max(pct_interest) AS MaxInterest
FROM yourTable GROUP BY caseID
) AS B ON A.caseID = B.caseID AND A.pct_interest = B.MaxInterest;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrflanne]

Gad that was FAST! Thanks a lot.
Jack