Help with query

[Keetso]

Hi.

I've created 4 separate queries that each lists the top salespeople (by total YTD commissions) depending on their office location. There are 4 different office locations (NYC, Chicago, Philly, Miami) hence the 4 different queries. I've put the name of the office location in the "criteria" area of each of the 4 query's "OffLoc" field.

What I'm trying to do is to have 1 query do the work of the existing 4 and give me the top salesperson for each separate office location. This new query is sorting each Office Location and top earners correctly, but the problem is that instead of listing each single top earner for each office location, I'm getting output like the following:

Chicago John Smith 32000
Chicago Mary Widow 28200
Chicago William Doe 16300
Miami Jane Jones 52000
Miami Curtis Weak 48500
Miami Jim Bow 22000
NYC Pocahantas 95000
NYC John Candy 82000
Philly Whoever 100000
Philly Whatsisname 22000

The query is sorting the table by total YTD commissions AND by Office Location, but what I need is to list JUST the top salesperson for each Office Location as follows:

Chicago John Smith 32000
Miami Jane Jones 52000
NYC Pocahantas 95000
Philly Whoever 100000

I'd be using this query to print a 4 page report (One Office Location/Name per page).

I've tried to set the "Top Values" selector to 1 (just gives me "Chicago John Smith 32000" and to 4 (gives me the top 4 for Chicago).

Can anyone tell me how to set this query up correctly before I pull what little is left of my hair? Here is the SQL for the query if that helps.

SELECT DISTINCT qryBrkrInfo.BrkrOffLoc, qryBrkrInfo.LNFN, Sum(qryBrkrInfo.BrokerFYC) AS SumOfBrokerFYC, Sum(qryBrkrInfo.BrokerORLS) AS SumOfBrokerORLS, Sum([BrokerFYC]+[BrokerORLS]) AS TotBrokerRevLS, Sum(qryBrkrInfo.BrokerFYCMS) AS SumOfBrokerFYCMS, Sum(qryBrkrInfo.BrokerORMS) AS SumOfBrokerORMS, Sum([BrokerFYCMS]+[BrokerORMS]) AS TotBrokerRevMS, Sum([BrokerFYC]+[BrokerFYCMS]) AS TotalSalesFYC, Sum([BrokerORLS]+[BrokerORMS]) AS TotalSalesBrokerOR, Sum([BrokerFYC]+[BrokerFYCMS]+[BrokerORLS]+[BrokerORMS]) AS TotalSalesBrokerRevenue
FROM qryBrkrInfo
GROUP BY qryBrkrInfo.BrkrOffLoc, qryBrkrInfo.LNFN
HAVING (((Sum([BrokerFYC]+[BrokerFYCMS]+[BrokerORLS]+[BrokerORMS]))<>0))
ORDER BY qryBrkrInfo.BrkrOffLoc, Sum([BrokerFYC]+[BrokerFYCMS]+[BrokerORLS]+[BrokerORMS]) DESC;


Many thanks!

K

 

[Golom]

This simplified version seems to do the trick. Just add your additional fields, Group by, etc.

SELECT C.City, C.Agent, C.Sales
FROM qryBrkrInfo C
WHERE C.Sales = (Select MAX(X.Sales) From qryBrkrInfo X WHERE X.City = C.City )

 

[Keetso]

Hi Golom.

Thanks for the rapid response!

I'm just a bit confused (and that ain't rare). Is it safe to assume that:

C.City = qryBrkrInfo.BrkrOffLoc
C.Agent = qryBrkrInfo.LNFN (concatenated from LN and FN fields)
C.Sales = &quot;TotalSalesBrokerRevenue&quot;

From my SQL statement?

I don't understand the &quot;X.Sales&quot;, etc then.

Possible to explain a bit further?

Again, thanks!!!

K

 

[Golom]

Yeah ... it's safe to assume that. I just set up a small test table and used names that were easier to type.

This thing

Select MAX(X.Sales) From qryBrkrInfo X WHERE X.City = C.City

is just called a correlated sub query. What it does is return the maximum sales number (&quot;TotalSalesBrokerRevenue&quot; for you) in a particular city (i.e &quot;BrkrOffLoc&quot in your table. We then (with the outer WHERE clause) select only those records in the main query that have a &quot;TotalSalesBrokerRevenue&quot; equal to that maximum.

Should work fine as long as you don't have more than one broker with exactly the same maximum TotalSalesBrokerRevenue. If you do then they will all appear.