Subquery , get latest price

[selvsagt]

Probably a simple question, but I cant get my mind to the logics here.. :banghead:
I have lots of products with changing prices. I need to get a list of all products with the latest price per product. I've done this in access with nested queries, but never in SQL before. I need a subquery (i think), and have been playing around for some time now.

These are the tables involved:

tblProd | product table, produkt is the productname
tblKurs | price table, NAV is the pricem and kursdato is the price date.

This is sort of what i want;

tblProd.produkt SisteKurs
"product 1" 10
"product 2" 8
"product 3" 7
"product 4" 15

This is what I have so far, but this only returns the latest price for product1 (not the rest of the products).

SELECT dbo.tblProd.Produkt, dbo.tblKurs.NAV AS Sistekurs, dbo.tblKurs.Kursdato
FROM dbo.tblProd INNER JOIN
dbo.tblKurs ON dbo.tblProd.prodID = dbo.tblKurs.prodID
WHERE (dbo.tblKurs.Kursdato =
(SELECT MAX(Kursdato) AS xkursdato
FROM dbo.tblKurs AS tblKurs_x))

 

[CJ_London]

try

 SELECT dbo.tblProd.Produkt, dbo.tblKurs.NAV AS Sistekurs, dbo.tblKurs.Kursdato
FROM dbo.tblProd INNER JOIN dbo.tblKurs ON dbo.tblProd.prodID = dbo.tblKurs.prodID
WHERE (dbo.tblKurs.Kursdato =(SELECT MAX(Kursdato) AS xkursdato
FROM dbo.tblKurs AS tblKurs_x [COLOR=red]WHERE prodid= dbo.tblProd.prodID[/COLOR] ))

 

[selvsagt]

Thank you !!!!!!!!!!!!!!!!!!!!!