How to identify highest sales by department for each store?

[MsAxes]

I have a table with about 3200 records at anytime of the month. There are three columns I look at, StoreNum, Dept and Sales. I would like to know which Dept that is in the same store had higher sales. Ex. for StoreNum123, Dept_B would win, so I need a field to return 'highest' or 'lowest' per StoreNum. I can do a Totals/Sum but not sure how I could identify the Dept w/the highest sales that is within the same store.

[June7]

Review http://allenbrowne.com/subquery-01.html#TopN

[Welshgasman]

Order by dept, sales DESC ?

[mike60smart]

Your Data table in not normalised.

You should have tables as shown in the attached screenprint:-

[MsAxes]

I tried:

SELECT Data.StoreNum, Data.Dept, Data.SalesFROM Data
 (
           SELECT StoreNum, Dept, Sum(Sales) as TotSales
           FROM Data As T2
           GROUP BY StoreNum, Dept, Sales) AS Test1
Inner Join Data.StoreNum = Test1.StoreNum;

and

SELECT Data.StoreNum, Data.DeptFROM Data
Join (
           SELECT StoreNum, Dept, Sum(Sales) as TotSales
           FROM Data As T2
           GROUP BY StoreNum, Dept, Sales) AS Test1
ON Data.StoreNum = Test1.StoreNum;

but get Syntax Error in FROM clause for both... not sure what I'm missing.

[Micron]

As posted, you have no space between Dept and FROM. That might just be a posting error.
4 views on your attached file already, so I didn't download - kinda late to the party.

[MsAxes]

Didn't see that - that wasn't causing the error though. I'm getting close except the sum as all of sales, I can't find examples (that I understand at least) that would help me split by store and dept.

SELECT *         FROM (SELECT StoreNum, Dept, Sum([Sales]) as TotSales
         FROM Data
         GROUP BY StoreNum, Dept) AS T2 INNER JOIN
Data as T1
ON T1.StoreNum = T2.StoreNum;

[June7]

Use your Query1 in another query:

SELECT Query1.*
FROM Query1 INNER JOIN (SELECT StoreNum, Max(SumOfSales) AS S FROM Query1 GROUP BY StoreNum) AS D
ON Query1.StoreNum = D.StoreNum AND Query1.SumOfSales = D.S;

[MsAxes]

I wasn't even close - thanks June7.