whole year rolling Forecast

[Ver101]

Guys!

I would like to create a query that would show 1 whole year of data assuming you have two tables 1) Actual and 2) forecast

Actual Table ---------> data up to June 2012

Forecast Table --------------> Jan - Dec 2012

I would like to have a query that would show combined tables as follows:


<TABLE style="WIDTH: 504pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=672 border=0><COLGROUP><COL style="WIDTH: 42pt" span=12 width=56><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" width=56 height=14>Jan</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Feb</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Mar</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Apr</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>May</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Jun</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Jul</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Aug</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Sep</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Oct</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Nov </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>Dec</TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14>Act</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Act</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Act</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Act</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Act</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Act</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Forecast</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Forecast</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Forecast</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Forecast</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Forecast</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Forecast</TD></TR></TBODY></TABLE>

Here is the complication, if actual table for a specific month has a data (june Actual) the query would no longer get the june from Forecast Table and it would now get data from July to Dec 2012

I badly need this query (available VB for this)

Thanks to all.

 

[SydneyGeek]

Untested but the SQL would look something like this:

SELECT [Fields] FROM [Actual Table] 
WHERE [Year]=2012
UNION ALL 
SELECT [Fields] FROM [Forecast Table] 
WHERE [Year]=2012
AND [Your Date Field]> (SELECT MAX([Date Field] FROM [Actual Table])

Denis

 

[Ver101]

Untested but the SQL would look something like this:

SELECT [Fields] FROM [Actual Table] 
WHERE [Year]=2012
UNION ALL 
SELECT [Fields] FROM [Forecast Table] 
WHERE [Year]=2012
AND [Your Date Field]> (SELECT MAX([Date Field] FROM [Actual Table])

Denis

thanks. I will try this one...

 

[Ver101]

thanks. I will try this one...

Hi Dennis,

the [your date field] is the criteria date?

let say i can be a field in form?

it could be [forms]![Form Name]![txt date]

 

[SydneyGeek]

Yes, the [Your date field] is the criteria date.
It could be a control on a form (as you mentioned) or the date field in the Actual table.

Denis