get to Last price for each product and each suppliers (1 Viewer)

ayamnash

New member
Local time
Today, 14:21
Joined
Sep 23, 2016
Messages
29
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
query1.png

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
I want the same result on ms-access
As you know, ms access does not accept this statement ...,ROW_NUMBER() over(partition by
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:21
Joined
Feb 19, 2013
Messages
16,553
I presume you mean latest not last, which means something else

Create an aggregate query for max date grouping on productid and supplierid

in another query, join this to the same table on date, supplierid and productid
 
Last edited:

ayamnash

New member
Local time
Today, 14:21
Joined
Sep 23, 2016
Messages
29
Yes latest not last.
Create...!
I will try when the pc is available
And add my comment
Thank in advance
 

Users who are viewing this thread

Top Bottom