I have gone out to other forums, checked out Microsoft Access information, I went out to Northwind and looked at how they are able to configure the Top 10 by query. Most of the top 10 only shows for all of your query. I am trying to figure out how to get the top 10 for each firm Id. I am attaching a zipped database showing the query that I am using. Any assistance with this would be greatly appreciated.
Clarification of my goal: Firm ID 260 top 10, Firm ID 261 top 10 and etc.
I am thinking I need to define my goals better for this issue. Possibly I did not communicate it properly. If I have 20 companies I want to find the top 10companies with the highest purchases out of the 20. Example:
June: Top 10 Companies:
July: Top 10 Companies out of 20 companies:
Company 9 $1000.00
I have sent a zipped db example. I guess it doesn't work for everyone. So I will provide more in depth discussion of my tables and query:
Explanation: Main form name: Monthly Summary. Subform: sfmTopTenRejects. Drop down combo for Month Selected.
The subform: sfmTopTenPurchase record source: (query name)TopTenRejects
Table for querry TopTenRejects - ActualFSTransactions
Within the query TopTenRejects the following fields are grouped: Date, DateID,Firm (the firm number), FirmName. I have an expression for sumum([PurchRejectCt,RedemptRejectCt, ExchangeRejectCt.])
Current SQL View of the query:
SELECT DISTINCTROW qryfirmrejects.Date, qryfirmrejects.FirmID, qryfirmrejects.Firm, qryfirmrejects.FirmName, Sum([PurchRejectCt]+[RedemptRejectCt]+[ExchangeRejectCt]+[TORARejectCt]+[ACATRejectCt]+[FundserveRejectCt]) AS [sum]
GROUP BY qryfirmrejects.Date, qryfirmrejects.FirmID, qryfirmrejects.Firm, qryfirmrejects.FirmName
ORDER BY Sum([PurchRejectCt]+[RedemptRejectCt]+[ExchangeRejectCt]+[TORARejectCt]+[ACATRejectCt]+[FundserveRejectCt]) DESC;
Let me know if you need more explanation.
Thanks for your time it is greatly appreciated.