I have Top query that shows the best value achieved for one product (eg TVS)
SELECT prevTop.*
from
(Select Top 1 s1.SaleMonth,
p1.ProdDesc,
a1.AreaCode,
a1.AreaDesc,
s1.Value
FROM (Sales AS s1
INNER JOIN Product as p1 ON p1.ProdCode = s1.Prod_fk)
INNER JOIN Area AS a1 ON a1.AreaCode = s1.Area_fk
where p1.ProdCode = 100
ORDER BY Value DESC)
as prevTop

select s.salemonth
,p.proddesc
,s.area_fk
,t.area_fk as toparea
,t.[Value] as topvalue
from sales as s
inner join product as p
on p.prodcode=s.prod_fk
inner join (select t2.prod_fk
,max(t2.area_fk) as area_fk
,t2.[value]
from (select prod_fk
,max([value]) as [value]
from sales
group by prod_fk
) as t1
inner join sales as t2
on t2.prod_fk=t1.prod_fk
and t2.[value]=t1.[value]
group by t2.prod_fk
,t2.[value]
) as t
on t.prod_fk=s.prod_fk
where s.salemonth='Sep-17'
;

Googling this error, I found that Access doesn't like it, when we alias to an already existing field name. Try this:

Query

select s.salemonth
,p.proddesc
,s.area_fk
,t.area_fk as toparea
,t.[Value] as topvalue
from sales as s
inner join product as p
on p.prodcode=s.prod_fk
inner join (select t2.prod_fk
,max(t2.area_fk) as area_fk
,t2.[value]
from (select prod_fk
,max([value]) as [maxvalue]
from sales
group by prod_fk
) as t1
inner join sales as t2
on t2.prod_fk=t1.prod_fk
and t2.[value]=t1.[maxvalue]
group by t2.prod_fk
,t2.[value]
) as t
on t.prod_fk=s.prod_fk
where s.salemonth='Sep-17'
;

- Fantastic that works! Just some minor mods with bracketing for Access syntax

select s.salemonth
,p.proddesc
,s.area_fk
,t.area_fk as toparea
,t.[Value] as topvalue
from (sales as s
inner join product as p
on p.prodcode=s.prod_fk)
inner join (select t2.prod_fk
,max(t2.area_fk) as area_fk
,t2.[value]
from (select prod_fk
,max([value]) as [maxvalue]
from sales
group by prod_fk
) as t1
inner join sales as t2
on t2.prod_fk=t1.prod_fk
and t2.[value]=t1.[maxvalue]
group by t2.prod_fk
,t2.[value]
) as t
on t.prod_fk=s.prod_fk
WHERE format(s.salemonth, "mmm-yy")="Sep-17";