By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

Choose latest data for report

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.