Dynamic Monthly Crosstab report wih actual monthly names? 1

[supportservice]

Greetings

I found this great article:

http://tek-tips.com/faqs.cfm?fid=5466

And wanted to see if someone could expand on that?

I would like to set the date range to always show one month prior + 5 equaling 6 months with the relative dates but to show the actual month names with the year in the cross-tab.

If the report is ran in Jan-12 the prior month should be Dec-11 which should show first as the first column and the following months Jan-12, Feb-12, Mar-12, Apr-12, May-12.

What I have now working with the above FAQS, this is my current crosstab query:
TRANSFORM Sum(Forecasts.ForecastAmount) AS SumOfForecastAmount
SELECT Forecasts.JobNumber
FROM Forecasts
WHERE (((Forecasts.ForecastDate) Between #7/1/2011# And Date()))
GROUP BY Forecasts.JobNumber, Forecasts.ForecastDate
ORDER BY Forecasts.ForecastDate
PIVOT "Mth" & DateDiff("m",[ForecastDate],Date());

1. Would like the date range to be dynamic so that it does not have to get manually changed and have no manual input. Just need the query / report to always run for 6 months starting from the prior month of the current year.
2. To have relative months with actual names without manual input on the report.

This is my current result: (NOTE: I left the ForecastDate in to show you the dates)

JobNumber	ForecastDate	Mth0	Mth1	Mth2	Mth3	Mth6
test1	9/1/2011					$800.00
test2	12/1/2011				$100.00	
test1	1/1/2012			$5,000.00		
test1	2/1/2012		$300.00			
test2	3/10/2012	$400.00

 

[dhookom]

I think the follwoing should work if you are always looking for the same relative window of months.

I would change the SQL to reference the previous month:

TRANSFORM Sum(Forecasts.ForecastAmount) AS SumOfForecastAmount
SELECT Forecasts.JobNumber
FROM Forecasts
GROUP BY Forecasts.JobNumber, Forecasts.ForecastDate
ORDER BY Forecasts.ForecastDate
PIVOT "Mth" & DateDiff("m",Date(), DateAdd("m",1,[ForecastDate])) IN ("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5");

This query should give last month's results under Mth0 and Mth5 will be future.

Then in your report, you can set the control sources of your text box headings to:
[tt][blue]
=DateAdd("m",-1,Date())
=DateAdd("m",0,Date())
=DateAdd("m",1,Date())
=DateAdd("m",2,Date())
=DateAdd("m",3,Date())
=DateAdd("m",4,Date())
[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[supportservice]

Perfect! Thank you very much!

One last thing on DateDiff and DateAdd

SELECT Forecasts.JobNumber, Sum(IIf([ForecastDate] Between #1/1/2010# And #12/31/2010#,[ForecastAmount],0)) AS PriorYear, Sum(IIf([ForecastDate] Between #1/1/2011# And #12/31/2011#,[ForecastAmount],0)) AS LastYear, Sum(IIf([ForecastDate] Between #1/1/2012# And Date(),[ForecastAmount],0)) AS YTD
FROM Forecasts
WHERE (((Forecasts.ForecastDate) Between DateDiff("y",Date(),DateAdd("y",-1,Date())) And Date()))
GROUP BY Forecasts.JobNumber;

How do I get the PriorYear, LastYear, and YTD with dynamic ranges so it doesn't have to be changed manually each year in the query?
And how to dynamically limit to two years prior until current year to date?

I tried the DateDiff and DateAdd to limit to two full years and current year to date but it doesn't seem to be working properly.
Or should it be DateSerial? If so how?

Thank you in advance.

 

[dhookom]

Try:

SELECT Forecasts.JobNumber, 
Sum(Abs(Year([ForecastDate]) = Year(Date())-2) * [ForecastAmount]) AS PriorYear, 
Sum(Abs(Year([ForecastDate]) = Year(Date())-1) * [ForecastAmount]) AS LastYear, 
Sum(Abs(Year([ForecastDate]) = Year(Date())) * [ForecastAmount]) AS YTD 
FROM Forecasts
WHERE ForecastDate Between DateAdd("y",-2,Date()) And Date()
GROUP BY Forecasts.JobNumber;

You could also use DateSerial(Year(Date())-2,1,1) to return Jan 01 from two years ago.

Duane
Hook'D on Access
MS Access MVP

 

[supportservice]

Thank you very much.

This works:

SELECT Forecasts.JobNumber, Sum(Abs(Year([ForecastDate])=Year(Date())-2)*[ForecastAmount]) AS PriorYear, Sum(Abs(Year([ForecastDate])=Year(Date())-1)*[ForecastAmount]) AS LastYear, Sum(Abs(Year([ForecastDate])=Year(Date()))*[ForecastAmount]) AS YTD
FROM Forecasts
WHERE (((Forecasts.ForecastDate) Between DateSerial(Year(Date())-2,1,1) And Date()))
GROUP BY Forecasts.JobNumber;

Note sure why

 Between DateAdd("y",-2,Date()) And Date()

produced null results but the DateSerial works! Thank you again.

 

[dhookom]

The DateAdd() should have been DateAdd("yyyy",...) .

Duane
Hook'D on Access
MS Access MVP