How to get last purchase price depend upon date in MSAccess

Question

Dear I have table called Trn_Purchase fields are ItemCode ItemDesc PurchaseDates Price I want to get the last purchase price depend on date table have e.g below data ItemCode PurchaseDate Price A 01-Mar-2010 4 A 02-Mar-2011 6 B 02-Mar-2015 2 B 02-Mar-2013 10 then result should itemCode PurchaseDate Price A 02-Mar-2011 6 B 02-Mar-2015 2 How to do this Thanks Basit.

ali tuncer · Answer

since you have two price on the same day for the same item it will show both of them..if you keep time it will show the latest one..

basit khan · Answer

Dear if data something like this Trn_Purchase2 Itemcode PurchaseDate Price A 01/03/2010 4.000 A 02/03/2011 6.000 A 01/03/2010 4.000 A 02/03/2011 10.000 B 02/03/2015 2.000 B 02/03/2013 10.000 B 02/03/2015 2.000 B 02/03/2013 10.000 then what will be the query. Thanks Basit

ali tuncer · Answer

try this : select p.ItemCode p.PurchaseDatep.Price from dbo.Trn_Purchase p inner join (select ItemCodemax(PurchaseDate) as Maxdate from dbo.Trn_Purchase group by ItemCode) t on p.ItemCode = t.ItemCode and p.PurchaseDate = t.Maxdate

ItemCode	PurchaseDate	Price
A	01-Mar-2010	4
A	02-Mar-2011	6
B	02-Mar-2015	2
B	02-Mar-2013	10