Select Top 5

[Michael]

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[LTofsrud]

Michael,

Since I didn't have the same tables as you did, I created something similar
in terms of tables and queries that might be of help. The query that I came
up with that gave me the answer was:

SELECT TOP 5 [FirstName]+" "+[LastName] AS Salesmen, Sum(Sales.OrderAmount)
AS [Sum of Orders]
FROM Salesmen INNER JOIN Sales ON Salesmen.SalesmenIdentifier =
Sales.SalesmenIdentifier
GROUP BY [FirstName]+" "+[LastName]
ORDER BY Sum(Sales.OrderAmount) DESC;

Nothing really special going on here. There is a foreign key in the Orders
table that links back to the Salesmen table. All the query displays is a
concatonation of the First and Last Name of the salesperson and the total of
all their sales.

When I query ALL of the results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55
Chris Evans $26.00
Sam Mathers $19.00

When I query the TOP 4 results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55

I noticed that you are not totalling the sum of all of the orders... was
that intentional?

HTH and good luck,
LT

 

[Michael]

thank you, but i'm also looking to include the Item sold:

for example using your data below -

item Salesmen Sum of Orders
xbox Bill Gates $465.00
xbox Bob Maluga $85.00
xbox John Doe $61.00
xbox Trevor Johnson $40.59
xbox Suzy Smith $38.55
xbox Chris Evans $26.00
xbox Sam Mathers $19.00

item Salesmen Sum of Orders
office software Bill Gates $200.00
office software Bob Maluga $65.00
office software John Doe $45.00
office software Trevor Johnson $34.59
office software Suzy Smith $38.55
office software Chris Evans $16.00
office software Sam Mathers $9.00

Michael,

Since I didn't have the same tables as you did, I created something similar
in terms of tables and queries that might be of help. The query that I came
up with that gave me the answer was:

SELECT TOP 5 [FirstName]+" "+[LastName] AS Salesmen, Sum(Sales.OrderAmount)
AS [Sum of Orders]
FROM Salesmen INNER JOIN Sales ON Salesmen.SalesmenIdentifier =
Sales.SalesmenIdentifier
GROUP BY [FirstName]+" "+[LastName]
ORDER BY Sum(Sales.OrderAmount) DESC;

Nothing really special going on here. There is a foreign key in the Orders
table that links back to the Salesmen table. All the query displays is a
concatonation of the First and Last Name of the salesperson and the total of
all their sales.

When I query ALL of the results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55
Chris Evans $26.00
Sam Mathers $19.00

When I query the TOP 4 results I get:
Salesmen Sum of Orders
Bill Gates $465.00
Bob Maluga $85.00
John Doe $61.00
Trevor Johnson $40.59
Suzy Smith $38.55

I noticed that you are not totalling the sum of all of the orders... was
that intentional?

HTH and good luck,
LT

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[KARL DEWEY]

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

 

[KARL DEWEY]

Sorry, I thought it was too easy - resulting data wrong.

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Michael]

Thank you Karl and Gary for replying.
i'm still having trouble. i cannot get this queries to run.
when i apply the code below and try to run the query, i see the query
calculating in the bottom left of the screen, when then hour glass goes away
- the sql page remains and i dont get a data set returned...

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[KARL DEWEY]

Did you edit it to use your table name instead of [LTofsrud] as I did?

Thank you Karl and Gary for replying.
i'm still having trouble. i cannot get this queries to run.
when i apply the code below and try to run the query, i see the query
calculating in the bottom left of the screen, when then hour glass goes away
- the sql page remains and i dont get a data set returned...

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[Michael]

i edited it for my query name...then i renamed my query [LTofsrud] to see if
that was the problem.

does it matter that im using a query and not a table as the data source?

Did you edit it to use your table name instead of [LTofsrud] as I did?

Thank you Karl and Gary for replying.
i'm still having trouble. i cannot get this queries to run.
when i apply the code below and try to run the query, i see the query
calculating in the bottom left of the screen, when then hour glass goes away
- the sql page remains and i dont get a data set returned...

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;


:

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;

 

[KARL DEWEY]

A table or query should not matter. Check for hard returns that the posting
and pasting might have added. More than that I do not know.

i edited it for my query name...then i renamed my query [LTofsrud] to see if
that was the problem.

does it matter that im using a query and not a table as the data source?

Did you edit it to use your table name instead of [LTofsrud] as I did?

Thank you Karl and Gary for replying.
i'm still having trouble. i cannot get this queries to run.
when i apply the code below and try to run the query, i see the query
calculating in the bottom left of the screen, when then hour glass goes away
- the sql page remains and i dont get a data set returned...

:

Try this --
SELECT T.Item, T.Orders, T.Salesmen, (SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders) AS Rank
FROM LTofsrud AS T
WHERE ((((SELECT COUNT(*)
FROM [LTofsrud] T1
WHERE T1.Item = T.Item
AND T1.Orders >= T.Orders))<6))
ORDER BY T.Item, T.Orders DESC;


:

Hello,
i'm looking to find the top 5 sales by salesmen for each item sold. below
is my query - it is returning just the top 5 salesmen.

any help would be appreciated:

SELECT TOP 5
Query2.Item,
Query2.Salesmen, Query2.Sales
FROM Query2
ORDER BY Query2.Sales DESC;