Outstanding Worker

[Micky]

Hey,

I have visited this web site for a several times in the last few days, I found it helpful.
So first I would like to say thank you all for visiting here and helping.
I'm now building a toys store, and would like to make a query of the outstanding worker. The one who sold the most (in money).
I know the basis of SQL language, but I cannot understand something and I'm stuck.
I have created a query named: "Total Sales Per Worker", in this query I see in each row the sales person's ID, name, and sum of his sales.
Now I would like to get the best worker. The one who sold the most. I couldn't do it with a query, or I don't know exactly how to do it.
Because I'm getting the max sales, but with a different worker's name. Do I need to use VB? Can you please advise?

Thanks!

[Bob Fitz]

Just set the top setting to 1. It's on a toolbar when the query is in design mode, in A2003.

[jzwp11]

You will need a couple of additional queries. First, create a query that finds the Max sales from your query Total Sales Per Worker

query name: qryMaxSales
SELECT Max([Total Sales Per Worker].SumOfSaleAmt) AS MaxOfSumOfSaleAmt
FROM [Total Sales Per Worker];

Now, create another query that joins your Total Sales Per Worker to qryMaxSales and join by the total sales field in both

SELECT [Total Sales Per Worker].Salesperson, qryMaxSales.MaxOfSumOfsaleamt
FROM [Total Sales Per Worker] INNER JOIN qryMaxSales ON [Total Sales Per Worker].SumOfsaleamt = qryMaxSales.MaxOfSumOfsaleamt;

[jzwp11]

With respect to Bob's post, you may have to order the records by the sum field (descending) in your Total Sales Per Worker query in addition to using the TOP predicate. That would look something like this (you would have to substitute your own table and field names)

SELECT TOP 1 Table5.Salesperson, Sum(Table5.saleamt) AS SumOfsaleamt
FROM Table5
GROUP BY Table5.Salesperson
ORDER BY Sum(Table5.saleamt) DESC;

[Micky]

Thanks All and especially jzwp11.
I did it how you explained, it didn't give me the result when I only sorted the list by worker's sales descending, and I had use an additional query.

[jzwp11]

Did you include the Top 1 as Bob suggested in the SELECT clause in addition to the sort order?

SELECT TOP 1 Table5.Salesperson, Sum(Table5.saleamt) AS SumOfsaleamt
FROM Table5
GROUP BY Table5.Salesperson
ORDER BY Sum(Table5.saleamt) DESC;

[Micky]

I'm using the 2010 version. I don't understand what do you mean by "Top 1". But it actually works the way I did.

[jzwp11]

I understand that there are multiple ways of getting to a solution, so what you have done is fine. I was just taking Bob's suggestion and refining it. The TOP x is something that can be done within a query to select only the specific number of records of the ones returned by the query. If you are working in design grid view of the query, you would open up the property sheet of the query and under Top Values property you would put in a 1.

You can also switch from design grid view to SQL view and just type in TOP 1 after the word SELECT.

[Micky]

Thanks. it's more complicated, I sorted the worker's list Ascending in the design view but I couldn't find the "Top values Property" in the design view.

[jzwp11]

I had trouble finding it at first as well. Open the query in design grid view. Then open the property sheet; it should say up at the top: Property Sheet Selection type: Field Properties Now click anywhere in the upper window of the design grid view (except do not click on the table), the property window should now change to Selection type: Query Properties. You should see the Top Values property as the 4th property in the list

[Micky]

Thanks for the detailed explanation and for your patience. I did find it now, in it does work both ways.
I actually prefer the second way, because it achieves the same goal but with less complications.
Thanks

[jzwp11]

You're welcome. Good luck with your project.