Display the top 5 donors

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?
gautonAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
'You may have your query and filter the records in your report.
'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
 
cjswimmerCommented:
try:

SELECT DonorID, State, Contribution
FROM tblDonors
GROUP BY DonorID, State, Contribution
ORDER BY State, Contribution DESC;
0
 
cjswimmerCommented:
woops, forgot the top predicate, hold on a sec...

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
nico5038Commented:
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
 
cjswimmerCommented:
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
 
BarryBulsaraCommented:
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
 
cjswimmerCommented:
but gauton needs to split it up by state, not just list the top 5 overall...
0
 
nico5038Commented:
hnasr,

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

Nic;o)
0
 
gautonAuthor Commented:
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
 
nico5038Commented:
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
 
hnasrCommented:
gauton,
Do u mean the "if condition" in the report?
0
 
hnasrCommented:
What about excluding the null values in the query?
0
 
gautonAuthor Commented:
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
 
hnasrCommented:
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
All Courses

From novice to tech pro — start learning today.