Choose latest data for report

[francophone77]

I am given a sales forecast every month for certain products which
usually extend a year. I enter each forecast into a "tblForecast"
which contains the sales forecast information and the date the forecast
was entered.
On later forecasts, entries are doubled due to overlapping of records
(ie. May 06 sales = 25 (date entered is Nov 05), May 06 sales = 40
(date entered is Dec 05). ForecastMonth, Qty and DateEntered are items
in the table.
I'd like to create a report which only grabs the latest forecast as it
is usually the most accurate. But I want to keep all forecast records
to show accuracy of past forecasts and variances if future forecasts.

What is the best way to create this report?

TIA

[Terry Kreft]

Something like

SELECT a.ForecastMonth, a.DateEntered, a.Qty
FROM tblForecast a
INNER JOIN (
SELECT b.ForecastMonth, Max(b.DateEntered) AS MaxOfDateEntered
FROM tblForecast b
GROUP BY b.ForecastMonth
) As c
ON
a.ForecastMonth = c.ForecastMonth
AND
a.DateEntered = c.MaxOfDateEntered

--
Terry Kreft

"francophone77" <fr***********@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I am given a sales forecast every month for certain products which
usually extend a year. I enter each forecast into a "tblForecast"
which contains the sales forecast information and the date the forecast
was entered.
On later forecasts, entries are doubled due to overlapping of records
(ie. May 06 sales = 25 (date entered is Nov 05), May 06 sales = 40
(date entered is Dec 05). ForecastMonth, Qty and DateEntered are items
in the table.
I'd like to create a report which only grabs the latest forecast as it
is usually the most accurate. But I want to keep all forecast records
to show accuracy of past forecasts and variances if future forecasts.

What is the best way to create this report?

TIA