Perfect, thanks so much Ajax.
Got it working with a few tweaks. Would not have figured this. Must now try understand the joining sql.
Thanks again
Code:
SELECT marketValue.InvestmentFK, Max(marketValue.MarketValueDate) AS MaxDate
FROM marketValue
GROUP BY marketValue.InvestmentFK;
and
Code:
SELECT marketValue.InvestmentFK, marketValue.MarketValueDate, sum(marketValue.marketValue) AS totalMarketValue
FROM marketValue INNER JOIN [Max Value Date] ON (marketValue.InvestmentFK=[Max Value Date].InvestmentFK) AND (marketValue.MarketValueDate=[Max Value Date].MaxDate)
GROUP BY marketValue.InvestmentFK, marketValue.MarketValueDate;