I am VERY new to databases, although have significant experience in Excel and use it thoroughly. It is apparent that my spread sheets are becoming too advanced and therefore i am looking at moving over to database. The majority of this has been simple but i have come against a snag. I am certain that others will have had this problem, but i cannot seem to search for the same in forums, at least not one that has been answered...
I have a table as follows
PLU Price Date
200002 £10.50 01/03/2014
200003 £11.25 01/03/2014
200004 £16.75 08/03/2014
200002 £9.50 08/03/2014
I would like a report to display all PLUs quoted, but ONLY returning the price with the most recent value. So in this case the report would show all info bar the first row, as the same PLU number is repeated in row 4, with an updated price, which in this case is lower.
SELECT a.PLU, a.Price, a.Date
FROM (SELECT Tbl_Prices.PLU, Tbl_Prices.Price, Tbl_Prices.Date
FROM Tbl_Prices) AS a
INNER JOIN (SELECT Tbl_Prices.PLU, Max(Tbl_Prices.Date) AS [Date]
GROUP BY Tbl_Prices.PLU) AS b
ON (a.PLU = b.PLU) AND (a.Date = b.Date);