I have a sample database based on 3 tables (Sales, Products and Area). Sales show values of products sold each month in an area AREA
AreaCode AreaDesc
1 UK
2 GERMANY
3 FRANCE
4 ITALY
5 SPAIN
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
I need a query which outputs just the latest Sale Month (Sep-2017) and appends columns (Top Area, TopValue) showing the Top 1 value achieved for each of the three products from the previous months.
Are the only one row in sales per salemonth, products, area?
If so, this might be what you're looking for
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 top(1) with ties
prod_fk
,area_fk
,[value]
from sales
order by row_number() over(partition by prod_fk
order by [value] desc
)
) as t
on t.prod_fk=s.prod_fk
where s.salemonth='Sep-17'
;
Hi bitsmed,
Thanks for quick response.
There is only one row per month, area and product.
I forgot to mention my query needs to work with MS Acess 2013
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 top 1
prod_fk
,area_fk
,[value]
from sales
order by [value] desc
)
as t
on t.prod_fk=s.prod_fk
where format(s.salemonth, "mmm-yy")="Sep-17"
;
This only returns one row for
salemonth proddesc area_fk toparea topvalue
Sep-17TVS 5 2 600
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'
;
To get round the circular reference error I removed the Max function but that doesn't restrict the rows returned to 1 per product
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,
[value]
from sales
group by prod_fk, [value]
) 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 format(s.salemonth, "mmm-yy")="Sep-17";
> salemonth proddesc area_fk toparea topvalue
> Sep-17 TVS 5 4 500
> Sep-17 TVS 5 2 600
> Sep-17 COMPUTERS 2 5 240
> Sep-17 COMPUTERS 2 3 330
> Sep-17 MOBILES 3 3 350
> Sep-17 MOBILES 3 3 550
So question is how to make the MAX function work without giving me a circular reference in Access or another approach.
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'
;
I'm sorry about this, but I'm not used to work with Access.
- 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";
Thanks bitsmed for your persistence to get to the solution.