x
Solved

# Display the top 5 donors

Posted on 2001-07-20
Medium Priority
312 Views
I work for a nonprofit organization and we would like to be able to have a report based upon a query that would group the top 5 donors for each state and only display those five donors in the report.

The top 5 donors would be based upon their highest contribution.

What is the best way of doing this?  Would I use the max function and how would I display only five records?
0
Question by:gauton
• 4
• 4
• 3
• +2

LVL 6

Expert Comment

ID: 6302157
try:

SELECT DonorID, State, Contribution
FROM tblDonors
GROUP BY DonorID, State, Contribution
ORDER BY State, Contribution DESC;
0

LVL 6

Expert Comment

ID: 6302161
woops, forgot the top predicate, hold on a sec...

0

LVL 54

Expert Comment

ID: 6302257
The top 5 is done by adding TOP 5 to the select like:
SELECT TOP 5 DONORS from tblDonors;

However, you need them per state...

This asks for a counter that's tested.
I would make two queries:
1) Sequence the donors per state
2) Filter for sequence <= 5

1) Would look like:
SELECT donorid, state, liters, dcount("donorid","tblDonor","state=" & [state] & "' AND liters >=" & [liters]) AS Rank FROM tblDonors;

2) a simple select like:
SELECT donorid, state, liters, Rank FROM qryRankedDonors WHERE Rank <= 5;

Clear ?

Nic;o)
0

LVL 6

Expert Comment

ID: 6302282
ok here's what I did.  I made a query called qryDonorStates with the following SQL:

SELECT State FROM tblDonors GROUP BY State

and then used the following SQL to get the top 5 from each state:

SELECT Q.State, D.DonorID, D.Contribution
FROM tblDonors D INNER JOIN qryDonorStates Q ON (D.State = Q.State) WHERE (D.DonorID In (SELECT TOP 5 DonorID FROM tblDonors WHERE (State=Q.[State]) ORDER BY DonorAmt DESC)) ORDER BY Q.state, D.DonorAmt DESC;
0

Expert Comment

ID: 6302347
Select Top 5 OrgName, Id from Donations Where Year = 2001 Order By donation DESC

Or just order by with DESC, then read down the list, but top n is provided specifically for this.

0

LVL 6

Expert Comment

ID: 6302424
but gauton needs to split it up by state, not just list the top 5 overall...
0

LVL 31

Accepted Solution

hnasr earned 300 total points
ID: 6306895
'You may have your query and filter the records in your report.
' Add an unbound field Seq to ur report:
'   Name: Seq
Control source=-1
'   Running sum: Over Group

'   Sort and Group on State, Group header Yes
'   Sort on donation desc

'In detail section of the report (On Format Event) add:

Detail.visible = true
If Me!Seq > 5 then
Detail.visible = False
End if
0

LVL 54

Expert Comment

ID: 6307328
hnasr,

This will make report processing a lot slower...
(Already aware that the report proesses all rows at least twice !)

Nic;o)
0

Author Comment

ID: 6308773
Nico5038,

I like the report suggestion, I tried it and it does work; however, if the condition is null I'm receiving a run-time error.  How can this be avoided?
0

LVL 54

Expert Comment

ID: 6309057
The Null is avoided by:
SELECT donorid, state, liters, dcount("donorid","tblDonor","state=" & [state] & "' AND NZ(liters) >=" &
NZ([liters])) AS Rank FROM tblDonors;

or by placing a default of zero liters on your input form.

BTW the "speed" comment still stands.....

Nic;o)

0

LVL 31

Expert Comment

ID: 6309689
gauton,
Do u mean the "if condition" in the report?
0

LVL 31

Expert Comment

ID: 6310198
What about excluding the null values in the query?
0

Author Comment

ID: 6312549
Not sure if that would work.  What I have done is in the query enter the condition to choose a state from a lookup table and then that would be the condition to populate the report with the top 5 donors.

It works as long as the condition is true, but lets say that we do not have any high dollar donors in IL, then that is when I receive the run time error.

0

LVL 31

Expert Comment

ID: 6370513
Sorry! I am away for some time from office.  I cannot try things now. I tried the report in Access 97 with no errors if a state has no high dollar donors. Pls list the records that work fine and those which produce the error. Thanks!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.