MAX Value Across Multiple Rows.

[Muse]

Need to get the top diagnosis in terms of paid dollars for each member for
the data set below. I tried using MAX and group but it returns the records
that have the highest paid $ for each diag. I need it to return one line for
each member with the diag code that has the highest paid $. TIA.

MEM# DIAG PAID

123 XXA $150
123 XXB $25
124 XXD $200
124 XXD $100
124 XXE $250


Need the following Result Set:

 

[KARL DEWEY]

By not using subqueries use three queries --
Save this query as Daig_Sum
SELECT [MEM#], [DIAG], Sum([PAID]) AS Paid_Sum
FROM YourTable
GROUP BY [MEM#], [DIAG];

Save this query as Max_Daig_Sum
SELECT [MEM#], Max([Paid_Sum]) AS Max_Daig_Sum
FROM Daig_Sum
GROUP BY [MEM#];

SELECT [MEM#], [DIAG], [Max_Daig_Sum].[Max_Daig_Sum]
FROM YourTable INNER JOIN Max_Daig_Sum ON YourTable.[MEM#] =
Max_Daig_Sum.[MEM#];