Max Query

[Guest]

There are three fields in my Table

Zone (values from 1 to 4)
Store# (text)
Sales (integer)

Basically I'd like to see the store with the Highest Sales for each zone

Within the Query, The Total Setting under the [Zone] and [Store#] Fields is
set to Group By. The Total setting under the [Sales] field is set to MAX.

When I run this query, it provides ALL the records. I'd like one record per
zone.

What am I doing wrong?

Thanks in advance.

 

[Guest]

hi,
the group by is cancelling the max
you have to be careful with using group by with aggragate
functions. the query will seem not to work but in a wierd
way it does. but because of group by, in drags in all the
groups too. in your case i guess it would be store. if you
droped store from the query, it would then group on zone.
so...
2 queries. 1st query just select max sales. add the 1st
query in 2nd query with the table and join on sales.
select table store and table zone query max sales.

 

[Guest]

Hi Al,

If you do not care about the Store in each Zone, remove it from your query
and group by. Right now, it is showing you for every Store in every Zone.

Help this helps.

 

[Marshall Barton]

There are three fields in my Table

Zone (values from 1 to 4)
Store# (text)
Sales (integer)

Basically I'd like to see the store with the Highest Sales for each zone

Within the Query, The Total Setting under the [Zone] and [Store#] Fields is
set to Group By. The Total setting under the [Sales] field is set to MAX.

When I run this query, it provides ALL the records. I'd like one record per
zone.

Grouping by zone and store will return a record for each
store. You need a different kind of query:

SELECT Zone, Store, Sales
FROM table
WHERE Store IN (SELECT TOP 1 T.Store
FROM table AS T
WHERE T.Zone = table.Zone
ORDER BY T.Sales DESC)

 

[Guest]

Thanks Marsh for your solution. That did the trick!

There are three fields in my Table

Zone (values from 1 to 4)
Store# (text)
Sales (integer)

Basically I'd like to see the store with the Highest Sales for each zone

Within the Query, The Total Setting under the [Zone] and [Store#] Fields is
set to Group By. The Total setting under the [Sales] field is set to MAX.

When I run this query, it provides ALL the records. I'd like one record per
zone.

Grouping by zone and store will return a record for each
store. You need a different kind of query:

SELECT Zone, Store, Sales
FROM table
WHERE Store IN (SELECT TOP 1 T.Store
FROM table AS T
WHERE T.Zone = table.Zone
ORDER BY T.Sales DESC)