Solved

Display the top 5 donors

Posted on 2001-07-20
14
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

617 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