Solved

# Display the top 5 donors

Posted on 2001-07-20
254 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

try:

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

LVL 6

Expert Comment

woops, forgot the top predicate, hold on a sec...

0

LVL 54

Expert Comment

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

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

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

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

LVL 30

Accepted Solution

hnasr earned 100 total points
'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

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

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

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 30

Expert Comment

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

LVL 30

Expert Comment

What about excluding the null values in the query?
0

Author Comment

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 30

Expert Comment

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…