Solved

Display the top 5 donors

Posted on 2001-07-20
14
285 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
ID: 6302157
try:

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

Expert Comment

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

0
 
LVL 54

Expert Comment

by:nico5038
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 6

Expert Comment

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

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

by:cjswimmer
ID: 6302424
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
ID: 6306895
'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
 
LVL 54

Expert Comment

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

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

by:nico5038
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 30

Expert Comment

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

Expert Comment

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

Author Comment

by:gauton
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 30

Expert Comment

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 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