Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    17

    Unanswered: Help With Top !0 by Group

    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.
    Attached Files Attached Files
    Karol

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "top X per Y" is a common requirement, but you also need to use Z, a column which has the values that determine the ordering that "top" operates on

    solution: a correlated subquery with a count(*)

    in english: select those rows where the number of rows in the same Y with a higher/lower Z is less than X

    we already have X=10, but i can't read your zip file, so how about if you just told us

    - the name of the table
    - the names of the columns you want returned
    - the Y column (firm_id?)
    - the Z column which determines the order
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    17

    Help With Top 10 Group

    OK. Here is the information for the query: qryfirmrejects

    Tables: tblMonth, tblFirm, tblActulFSTransactions

    Firm ID being what needs to be sorted with top 10 (by each firm)

    Data to be displayed for top 10 firms
    Colums: DateID, PurchRejectCt,RedemptRejectCt, ExchangeRejectCt.

    Thanks for your help.
    Karol

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think we're missing a key element - how do you define your top 10?

    If you have 200 records for firm 1 then which 10 do you want to display?
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whoa, whoa, whoa, hold on a second please

    why 3 tables? and top 10 by each firm of what?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    17

    Help With Top 10 by Group

    It would be the top 10 purchases amounts for each firm. The 3 tables make up the query.
    Karol

  7. #7
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    17

    Help With Top 10 by Group

    I will try to send an attachment again.
    Attached Files Attached Files
    Karol

  8. #8
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    17

    Help With Top 10 Group

    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:
    Company1 $400.00
    Coompany2 $450.00
    Company3 $500.00
    etc.

    July: Top 10 Companies out of 20 companies:
    Company 9 $1000.00
    etc.

    Did this make more sense???
    Karol

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Clarification of my goal: Firm ID 260 top 10, Firm ID 261 top 10 and etc.
    you no longer want top 10 for each firm?

    now you want top 10 firms for each month?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    17
    I apologize. I obviously am not communicating this very well. I want the top 10 firms purchases by month out of a total of 20 firms.
    Karol

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you provide some sample data and desired results?
    This would clear the issue up no end (hopefully)!
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    17

    Help With Top 10 by Group

    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]
    FROM qryfirmrejects
    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.
    Karol

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Errr - I'm kinda lost in this thread but I think the answer you want is:
    Code:
    SELECT TOP 10 someFields
         , Sum(someValue)
    FROM   someTable
    GROUP
        BY someValue
    ORDER
        BY Sum(someValue)
    George
    Home | Blog

  14. #14
    Join Date
    May 2007
    Posts
    14
    Hi Karol,

    So if I understand, you want query TopTenRejects to show top 10 firmNames based on sum: Sum([PurchRejectCt]+[RedemptRejectCt]+[ExchangeRejectCt]+[TORARejectCt]+[ACATRejectCt]+[FundserveRejectCt]).

    for period of date, month, week …

    This is possible in PivotTableView of your query “TopTenRejects”. You need only to select field FirmName click properties\filter and group…

    Am I guess your purpose?
    Attached Thumbnails Attached Thumbnails exT10.bmp  

  15. #15
    Join Date
    Apr 2002
    Location
    Kansas City, MO
    Posts
    17

    Help With Top 10 By Group

    Actually, I was trying to figure it out by query, so I could add the results in a subform. I wasn't looking for a pivot table as the subform.
    Karol

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •