Delores_C
asked on
Getting ten records only highest sums
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?
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.
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.
ASKER
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
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$([tblAutoprocessImp ortFUNDS]. [StatDate] ,'Short Date') AS [StatDate By Day], tblAutoprocessImportFUNDS. CODE, Sum(tblAutoprocessImportFU NDS.Count) AS SumOfCount
FROM tblAutoprocessImportFUNDS
GROUP BY tblAutoprocessImportFUNDS. Ptcg, tblAutoprocessImportFUNDS. TRNS, Format$([tblAutoprocessImp ortFUNDS]. [StatDate] ,'Short Date'), tblAutoprocessImportFUNDS. CODE
HAVING (((Format$([tblAutoprocess ImportFUND S].[StatDa te],'Short Date')) Between [start] And [end]))
ORDER BY Sum(tblAutoprocessImportFU NDS.Count) DESC;
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.
FROM tblAutoprocessImportFUNDS
GROUP BY tblAutoprocessImportFUNDS.
HAVING (((Format$([tblAutoprocess
ORDER BY Sum(tblAutoprocessImportFU
ASKER
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!
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!
If you don't want to see the Code value then remove it from the query...In design view just delete the column.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I decided to use a pivot table, and it gives me the results I need. thanks for all your effort.
ASKER
The information provided was very accurate, this is reason I decided to use a Pivotview to get my results
ASKER
Daily-Top-10-Autoprocess.accdb