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?
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?
Maybe in a query
SELECT TOP 10
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.
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.
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.
I can't seem to make this work...
I don't get an error, but I am still getting more than 10 from salespeople: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;
Any ideas?
Derp...Subquery wasn't search by salesperson.... wow.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;
all good!