access sql ,retrieiving maximum date with relative price
Posted on 2011-04-29
I am trying to retreive ,for the latest purchase of an item "xx" from every vendor ,the maximum date and the related price for that record .Please see the SQL:
" SELECT Max(vendoritemstable2.TRNSDATE) AS MaxOfTRNSDATE, Last(vendoritemstable2.PRICE) AS LastOfPRICE, vendoritemstable2.CARD
WHERE (((vendoritemstable2.ITEMCODE)='xx')) GROUP BY vendoritemstable2.CARD"
however the price I am getting for the maximum date is unrelated to that date ,it belongs to another records .I tried to use a select inside the select from ... but I didn't know how to do it .
I need to retrieve the fields for that record with maximum date in 1 select statement or in 1 process