pcoghlan
asked on
query needed to make table of top 10 orders for each sales person
I need to make a table that includes the top 10 records from another table. I think this analogy describes it quite well.
I have 3 fields in a table;
[SalesPerson] - [OrderNo] - [OrderAmount]
Each sales person has thousands of orders. I want to use a Make Table query that inserts the top 10 (by value) orders for each and every sale person. Its as simple as that!
I want to include all three fields in the new table that will effectively be a subset of records in the first table.
I hope I have made this clear!
Paul
I have 3 fields in a table;
[SalesPerson] - [OrderNo] - [OrderAmount]
Each sales person has thousands of orders. I want to use a Make Table query that inserts the top 10 (by value) orders for each and every sale person. Its as simple as that!
I want to include all three fields in the new table that will effectively be a subset of records in the first table.
I hope I have made this clear!
Paul
Last Comment
ASKER
Thanks, I created an identical table and I get a dialog box asking for the T2.OrderAmount Parameter?
I have put 1, 999999 and 0 ion here and let it continue and it appears to work regardless?
Interestingly the table is seemingly sorted by OrderNo DESC and not OrderAmount DESC which is obviously not a problem.
I have put 1, 999999 and 0 ion here and let it continue and it appears to work regardless?
Interestingly the table is seemingly sorted by OrderNo DESC and not OrderAmount DESC which is obviously not a problem.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Works like a dream, thanks!!
By the way, it is destined for a fixed output but I do take on board your comments about it being a bad basign if destined to remain within the database as its replicates existing data.
Paul
By the way, it is destined for a fixed output but I do take on board your comments about it being a bad basign if destined to remain within the database as its replicates existing data.
Paul
Glad to hear it - and you're welcome. :-)
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
(The inevitable duplicating data arguement).
However it could be for some exporting process or freestyle reporting/alteration of data.
SELECT
T1.[SalesPerson], T1.[OrderNo], T1.[OrderAmount] INTO NewTableName
FROM
tblOrders T1
WHERE T1.[OrderNo] IN
(SELECT TOP 10 [OrderNo]
FROM tblOrders T2
WHERE T1.[SalesPerson] = T2.[SalesPerson]
ORDER BY T2.[OrderAmount] DESC, T1.[OrderNo])
ORDER BY T1.[SalesPerson], T2.[OrderAmount] DESC