asked on

Visual studio - find maximum dollar amount in a field for each year

I have a table in visual studio that lists nonprofit grant recipients for a number of years.

How can I automatically pull the largest grant for each year. For example, my results would show Nonprofit2 with the amount for 2006 and Nonprofit5 with the amount for 2007.

I assume it's some kind of command with MAX, but I'm not sure how to write it.

Recipient Amount Year
Nonprofit1 $4,000 2006
Nonprofit2 $8,000 2006
Nonprofit3 $2,000 2006
Nonprofit4 $1,000 2007
Nonprofit5 $2,000 2007

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TinaSC

ASKER

Thanks. Amount is money.

I sort of understand what you have, but Im working with a complicated view to pull the fields and am struggling a bit with how to insert your suggestion.

Recipient (V_GRHistory.RecipientName), Amount (V_GRHistory.AdjustedAmount AS PayAmount) , and Year (YEAR(V_GRHist1.DatePaid) AS Year). (I probably should have included that the first time).

Can you help me incorporate your suggestions into this view (code attached)?

SELECT     
GRCharacteristics.Name, GRCharacteristicCodes.CodeValue, V_GRHistory.RecipientName, V_GRHistory.DatePaid, V_GRHistory.AdjustedAmount AS PayAmount, V_GRHistory.PayStatus, V_GRHistory.FundNumber, YEAR(V_GRHist1.DatePaid) AS Year

FROM         
V_GRHist1  
INNER JOIN GRGrantCharacteristics 
INNER JOIN V_GRHistory ON GRGrantCharacteristics.GrantId = V_GRHistory.GrantId ON V_GRHist1.GrantId = GRGrantCharacteristics.GrantId 
LEFT OUTER JOIN GRCharacteristicCodes ON GRGrantCharacteristics.CharacteristicCodeId = GRCharacteristicCodes.CharacteristicCodeId 
LEFT OUTER JOIN GRCharacteristics ON GRGrantCharacteristics.CharacteristicId = GRCharacteristics.CharacteristicId

Open in new window

Aneesh

is GrantId the primary key for V_GRHist1 ?

TinaSC

ASKER

Yes.

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial