Solved

Display the top 5 donors

Posted on 2001-07-20
14
254 Views
Last Modified: 2012-03-15
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
Comment
Question by:gauton
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
try:

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

Expert Comment

by:cjswimmer
Comment Utility
woops, forgot the top predicate, hold on a sec...

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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

by:cjswimmer
Comment Utility
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

by:BarryBulsara
Comment Utility
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

by:cjswimmer
Comment Utility
but gauton needs to split it up by state, not just list the top 5 overall...
0
 
LVL 30

Accepted Solution

by:
hnasr earned 100 total points
Comment Utility
'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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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

by:gauton
Comment Utility
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

by:nico5038
Comment Utility
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

by:hnasr
Comment Utility
gauton,
Do u mean the "if condition" in the report?
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
What about excluding the null values in the query?
0
 

Author Comment

by:gauton
Comment Utility
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

by:hnasr
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now