Max Values Query

  • Thread starter Thread starter Mike Lempel
  • Start date Start date
M

Mike Lempel

I have a table with fields: Month, Year, Store, SalesPerson, SalesDollars,
HrsWorked.

For each Month/Year there are many Stores, and many SalesPersons per store.

I would like to create a query that, for a particular Month and Year
(specified by query parameters), will provide a listing having one record
per store with the Salesperson, HrsWorked, and SalesDollars shown for the
SalesPerson having the highest SalesDollars. Assume SalesDollars are unique
(no ties for the max SalesDollars).

I would appreciate any suggestions regarding the 'best' way to do this.

Thanks,
Mike
 
Mike,

For simplicity, I would do this in two steps. First make a query to
identify the highest SalesDollars per Store. The SQL for this query
would look something like...
SELECT Store, Max([SalesDollars]) FROM YourTable GROUP BY Store
Then, make another query, with the main table, plus this first query,
joined on both the Store and SalesDollars fields. Then you just have to
include whichever fields you want in the query, and whack the criteria
in the SalesMonth and SalesYear fields (notice I have changed these...
the words Month and Year have a special meaning (they are called
reserved words) and should not be used as the name of a field or control).
 
Thanks, Steve.

The method you describe is the way I have typically done this. Do you know
of a way to do this with a single query which would be as (or almost as)
computationally efficient? Your 'For simplicity' preface implies that
there might be.

Thanks,
Mike

Steve Schapel said:
Mike,

For simplicity, I would do this in two steps. First make a query to
identify the highest SalesDollars per Store. The SQL for this query
would look something like...
SELECT Store, Max([SalesDollars]) FROM YourTable GROUP BY Store
Then, make another query, with the main table, plus this first query,
joined on both the Store and SalesDollars fields. Then you just have to
include whichever fields you want in the query, and whack the criteria
in the SalesMonth and SalesYear fields (notice I have changed these...
the words Month and Year have a special meaning (they are called
reserved words) and should not be used as the name of a field or control).

--
Steve Schapel, Microsoft Access MVP


Mike said:
I have a table with fields: Month, Year, Store, SalesPerson, SalesDollars,
HrsWorked.

For each Month/Year there are many Stores, and many SalesPersons per store.

I would like to create a query that, for a particular Month and Year
(specified by query parameters), will provide a listing having one record
per store with the Salesperson, HrsWorked, and SalesDollars shown for the
SalesPerson having the highest SalesDollars. Assume SalesDollars are unique
(no ties for the max SalesDollars).

I would appreciate any suggestions regarding the 'best' way to do this.

Thanks,
Mike
Click to expand...
Click to expand...
 
Mike,

The 'for simplicity' was for my sake as well as yours. :-) It is
possible to write this in a single SQL query, which would be easy for
some SQL gurus, but I would need to figure it out. I'll see if I can do
it later.
 
Steve said:
Mike,

The 'for simplicity' was for my sake as well as yours. :-) It is
possible to write this in a single SQL query, which would be easy for
some SQL gurus, but I would need to figure it out. I'll see if I can do
it later.
Click to expand...

No telling what the query optimizer makes of it... try both ways.
 
Mike,

Here's an idea...

SELECT a.Store, a.SalesPerson, a.SalesDollars, a.HrsWorked
FROM YourTable As a INNER JOIN
(SELECT Store, Max(SalesDollars) As c
FROM YourTable
GROUP BY Store) As b
ON a.Store=b.Store AND a.SalesDollars=b.c

For more information on this stuff, see
http://www.mvps.org/access/queries/qry0020.htm
 
Back
Top