Results 1 to 7 of 7
  1. #1
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287

    How to get the top 10 records for every salesperson


    Hey guys,

    I currently have a query chain that gets me every salesperson's sales, in descending order of dollar amount. I need to pull only the first (top) 10 for every salesperson. Whats the best way to do this?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe in a query
    SELECT TOP 10

  3. #3
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    But that only pulls the top 10 records. Not the top 10 for each salesperson. I can't make a separate query for every salesperson.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    Ah nice June7. Looking into that now. Query is currently running (extremely slowly) so I may have to populate a temp table before I roll this out.

  6. #6
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    I can't seem to make this work...

    Code:
    SELECT T_TBS_2.CountOfSO_DOC_NUM, T_TBS_2.SO_STORE_CD, T_TBS_2.FirstOfSO_EMP_SLSP_CD1, T_TBS_2.SumOfLinePrice, T_TBS_2.CUST_CD, T_TBS_2.MaxOfSTAT_CD, T_TBS_2.SO_WR_DT, T_TBS_2.MaxOfFINAL_DT, T_TBS_2.ORD_TP_CDFROM T_TBS_2
    WHERE T_TBS_2.CUST_CD IN
    (SELECT TOP 10 CUST_CD
    FROM T_TBS_2 AS Dupe
    WHERE Dupe.CUST_CD = T_TBS_2.CUST_CD
    ORDER BY Dupe.SumOfLinePrice DESC)
    ORDER BY T_TBS_2.SO_STORE_CD, T_TBS_2.FirstOfSO_EMP_SLSP_CD1, T_TBS_2.SumOfLinePrice DESC;
    I don't get an error, but I am still getting more than 10 from salespeople:
    Click image for larger version. 

Name:	Screen Shot 2014-07-31 at 10.57.26 AM.jpg 
Views:	6 
Size:	153.5 KB 
ID:	17590

    Any ideas?

  7. #7
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    287
    Derp...
    Code:
    SELECT T_TBS_2.CountOfSO_DOC_NUM, T_TBS_2.SO_STORE_CD, T_TBS_2.FirstOfSO_EMP_SLSP_CD1, T_TBS_2.SumOfLinePrice, T_TBS_2.CUST_CD, T_TBS_2.MaxOfSTAT_CD, T_TBS_2.SO_WR_DT, T_TBS_2.MaxOfFINAL_DT, T_TBS_2.ORD_TP_CDFROM T_TBS_2
    WHERE T_TBS_2.CUST_CD IN
    (SELECT TOP 10 CUST_CD
    FROM T_TBS_2 AS Dupe
    WHERE Dupe.FirstOfSO_EMP_SLSP_CD1= T_TBS_2.FirstOfSO_EMP_SLSP_CD1
    ORDER BY Dupe.SumOfLinePrice DESC)
    ORDER BY T_TBS_2.SO_STORE_CD, T_TBS_2.FirstOfSO_EMP_SLSP_CD1, T_TBS_2.SumOfLinePrice DESC;
    Subquery wasn't search by salesperson.... wow.

    all good!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-29-2014, 01:29 AM
  2. Replies: 2
    Last Post: 05-28-2013, 04:00 PM
  3. Replies: 1
    Last Post: 02-16-2013, 11:36 AM
  4. Replies: 1
    Last Post: 01-24-2013, 05:50 PM
  5. Access 2007 - Creating New Records Based on Existing Records
    By GeorgeAugustinePhillips in forum Forms
    Replies: 9
    Last Post: 10-05-2011, 05:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums