Access 2007 Forecast Data
Posted on 2012-03-27
Table1: ProjectID, M1, M2, M3... M60
Table2: Project Data including project start date.
Query1: Gives us today's date - project start date in months. So we take the difference between today's date and the project approval date to get the difference in months as a number 1-60. The reason 60 is the max number is because we have no projects longer than 5 years 5x12=60.
So we have a project table, with key = project number and M1-M60 forecast fields. These fields contain projected spend $ for that period.
We have a query which tells us a number of months it has been since the project was approved.
So visualize when we open the form we want to see the selected projects current month + next 11 months worth of forecast data.
So if the month difference between start month and current month is 12, we would want to see period M12-M24 from the table that contains M1-M60.
We are having a problem relating the information from the query to get the appropriate 12 month period to display in our form which ultimately will act as an update query so that our project managers can update their budget for the next 12 months as part of a rolling forecast design.
Can you offer me a solution to this problem that I can either build into my query or write in VBA as a function. I'm a novice at VBA, I'm fairly confident in my Access 2007 skills. I can provide more information if needed.
Basically this is like a Dlookup in my eyes, but I couldn't get that to work, maybe I wrote it wrong.
Thanks for your help.