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 "tblForecas t"
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]

Re: Choose latest data for report

Something like

SELECT a.ForecastMonth , a.DateEntered, a.Qty
FROM tblForecast a
INNER JOIN (
SELECT b.ForecastMonth , Max(b.DateEnter ed) AS MaxOfDateEntere d
FROM tblForecast b
GROUP BY b.ForecastMonth
) As c
ON
a.ForecastMonth = c.ForecastMonth
AND
a.DateEntered = c.MaxOfDateEnte red



--
Terry Kreft



"francophon e77" <francophone77@ hotmail.com> wrote in message
news:1132637225 .052790.93440@z 14g2000cwz.goog legroups.com...[color=blue]
>I am given a sales forecast every month for certain products which
> usually extend a year. I enter each forecast into a "tblForecas t"
> 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
>[/color]