Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting ten records only highest sums

Posted on 2011-09-12
10
Medium Priority
?
226 Views
Last Modified: 2012-06-21
The attached database has a report that provides a list of codes, and the count for each by a requested date range (using 5/1/2011 thru 5/31/2011).  My problem is I need to get the top ten (1) highest counts which is a sum field on the report.  I need the total count, listing the highest count of record to the lowest in the ten (10) codes that are counted.  Can someone help?
0
Comment
Question by:Delores_C
[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
  • 6
  • 4
10 Comments
 

Author Comment

by:Delores_C
ID: 36523757
Sorry, I forget to attach the file
Daily-Top-10-Autoprocess.accdb
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36523878
Access reports does not provide a means of sorting groups on group total values.

You can create a separate query to get the top N groups based on group totals.
Then join that query to your current recordsource and that will limit the report to the top 10. You can then sort the report on ther group totals obtained from the query.
0
 

Author Comment

by:Delores_C
ID: 36524089
I tried but, I am confused.  Could you use the database attached and put in an example of what you are suggesting.  Thanks in advance
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 77

Expert Comment

by:peter57r
ID: 36527760
I have looked at your query but I am afraid it makes no sense to me and since I don't know what your data is about I can't convert it into something I think is correct.

However I have made a couple of changes and these are included in the sql view below.
It looks like you should be summing your Count field , not your Code field.  I suggest you try this as new query rather than ovrwriting your existing query.

You should see if you run this query that you only get the top 10 sums of Count. I doubt if this is exactly what you are after, but as I say, I don't know what the aim is here so I can't do more at this point.
This example might give you an idea of how to get your answer.

SELECT  TOP 10 tblAutoprocessImportFUNDS.Ptcg, tblAutoprocessImportFUNDS.TRNS, Format$([tblAutoprocessImportFUNDS].[StatDate],'Short Date') AS [StatDate By Day], tblAutoprocessImportFUNDS.CODE, Sum(tblAutoprocessImportFUNDS.Count) AS SumOfCount
FROM tblAutoprocessImportFUNDS
GROUP BY tblAutoprocessImportFUNDS.Ptcg, tblAutoprocessImportFUNDS.TRNS, Format$([tblAutoprocessImportFUNDS].[StatDate],'Short Date'), tblAutoprocessImportFUNDS.CODE
HAVING (((Format$([tblAutoprocessImportFUNDS].[StatDate],'Short Date')) Between [start] And [end]))
ORDER BY Sum(tblAutoprocessImportFUNDS.Count) DESC;


0
 

Author Comment

by:Delores_C
ID: 36528686
Thanks for your effort.  You were right when indicating I should be summing on the Count, not the code.  

I used your statement above, and I see where it gives me the top ten of a date range, but codes are the same.  Is there a way to get the top ten codes for a date range, to include their totals?

I really appreciate your help!
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36528733
If you don't want to see the Code value then remove it from the query...In design view just delete the column.
0
 

Author Comment

by:Delores_C
ID: 36528754
I do need to see the Code, I need the code and the total for the code, I just don't need the same code.  I need something like the example:

Code                 Count              Date
8                         50                  5/1/11
402                    1715               5/1/11
9                         500                5/21/11

I am looking for the top 10 Codes, with their total Count for the date range I enter.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 36530614
You cannot avoid seeing the same code if you are grouping on more than one field.

the only way you can have a single record per code is if you don't group on any other field.
0
 

Author Comment

by:Delores_C
ID: 36531106
I decided to use a pivot table, and it gives me the results I need.  thanks for all your effort.
0
 

Author Closing Comment

by:Delores_C
ID: 36537311
The information provided was very accurate, this is reason I decided to use a Pivotview to get my results
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

721 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