Forms/Queries - Crosstab Query for dynamic form months

[valverde311]

Hi All,

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

I've been trying to recreate this report but have gotten stuck with a number of a things.

My forecast table has records of hours worked for each resource on a number of projects. In my table, fields are basically

[Project],[Name],[Location],[Jan 10], [Feb 10], .... [Dec 11] < where Jan 10 contains the total amount of hours worked for that resource.

Currently I have the form set-up to show the rest of 2010 - [July 10] - [Dec 10]. What I am trying to show is a rolling 12 month outlook into the current month + the 11 following months, so for insteance - in November, my users will open the form/report and see allocations from [Nov 10] - [Oct 2011].

Using the "dateadd" function, I was thinking that I could put a text box in my form that =Date() to show the current date. I also know how to get the current month and the month after

=DateAdd("m",0,me.txtCurrentDate)
=DateAdd("m",1,me.txtCurrentDate)
... and so on

This would work if my column headers were dates instead of text - 3/1/2010 instead of Mar 10 or 4/1/2010 instead of Apr 10.

So I thought that to get around this I should first make a reference table { where [Month] = Mar 10, Apr 10 and [Date] = 3/1/2010 , 4/1/2010 } and then build a query that brings [Project], [Name], [Location] from my forecast table, but then only brings in the respective Mar 10, Apr 10 column fields if they are within the range (current month - current month + 11).

However... I cannot figure out how to write the sql that brings in the next 12 months. I get stuck because Mar 10, Apr 10 are columns in my forecast table, but rows in my reference table.


Do you have any ideas? My sincere appologies if this is a stupid question.


Thanks in advance,

 

[Poppa Smurf]

This is my response to your posting on another forum --

Change your table design to [Project],[Name],[Location],[Forecast date]. Forecast date is the start of the month of the forecast. Then use your crosstab to produce the rolling twelve months