I have trans table contains Productid, price, date, supplerid feilds How can i get last price for each item and each suppliers as in picture
i have same database on sql server and i got help.
The sql statement works perfectly as bellow
I want the same result on ms-access
As you know, ms access does not accept this statement ...,ROW_NUMBER() over(partition by
i have same database on sql server and i got help.
The sql statement works perfectly as bellow
SQL:
select max(date) as date, productid, max(qty) as qty,max(price),supplierid
from
(
select productid,supplierid,qty,date,price,ROW_NUMBER() over(partition by productid, supplierid order by productid, supplierid,date desc) as row_num
from trans
) x
where row_num = 1
group by productid,supplierid
order by productid
As you know, ms access does not accept this statement ...,ROW_NUMBER() over(partition by