Solved

Display the top 5 donors

Posted on 2001-07-20
14
274 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
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.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 importing text files 13 21
Access query expression 6 20
Access VBA, adding Progress Bar in code to allow execution. 7 28
DCount Type Mismatch 2 22
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

770 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