Forecast CrossTab Query

[kagoodwin13]

I have been tasked with creating a forecast report in Access. This is something I think Excel does well, but I would like to integrate this functionality with an existing Access database if possible. I have attached the Excel spreadsheet for example. It may be easier to look at the spreadsheet than interpret my rambling below.



What I am trying to do is create a forecast of customer support tickets that will be opened in the next 12 months. This can then be used to calculate staffing (headcount) requirements, as well as anticipate growth in demand from new customers that will come on board later.

The number of tickets is determined by an average of previous data. I already have this figured out.

After the transition date (from design to support) has been met, I would like the query to add the average from one customer to the remaining customers, so that there is a progressive sum of average tickets. In the Excel file, it is the "Desired Results" tab.

I have made a cross-tab query to try to accomplish this, however the cross-tab query only copies the average for the month of the transition date itself, not every month after the transition date. In the Excel file, it is the "Current Results" tab.

Is there a way for the data to keep repeating after the transition date has been met? Should I use a different style of query other than cross-tab? Or is this even possible in Access at all?

Thanks in advance!

Ticket Forecast Example.zip

[ranman256]

Cross-tab seems the perfect way to present this data.

If you dont like that, then you can build a 'report' table, empty it , then append the data you need to it and run the report off that.

[kagoodwin13]

Had to get creative: I had to manually program my own columns, so that I could use the formula to fill in the forecast amounts in each month. The only downside is that it doesn't "roll": it does not remove columns as they pass, or add new ones in the future. But this works for now.

SQL query is:

SELECT qryACCOUNT_USER_CATEGORY2.Intacct_ID, qryACCOUNT_USER_CATEGORY2.Average_Tickets_Per_Month, tblTRANSITION_DATE.Transition_Date,
IIf([Transition_Date]<#9/1/2014#,[Average_Tickets_Per_Month],0) AS Sep14,
IIf([Transition_Date]<#10/1/2014#,[Average_Tickets_Per_Month],0) AS Oct14,
IIf([Transition_Date]<#11/1/2014#,[Average_Tickets_Per_Month],0) AS Nov14,
IIf([Transition_Date]<#12/1/2014#,[Average_Tickets_Per_Month],0) AS Dec14,
IIf([Transition_Date]<#1/1/2015#,[Average_Tickets_Per_Month],0) AS Jan15,
IIf([Transition_Date]<#2/1/2015#,[Average_Tickets_Per_Month],0) AS Feb15,
IIf([Transition_Date]<#3/1/2015#,[Average_Tickets_Per_Month],0) AS Mar15,
IIf([Transition_Date]<#4/1/2015#,[Average_Tickets_Per_Month],0) AS Apr15,
IIf([Transition_Date]<#5/1/2015#,[Average_Tickets_Per_Month],0) AS May15
FROM tblTRANSITION_DATE
     INNER JOIN qryACCOUNT_USER_CATEGORY2
     ON tblTRANSITION_DATE.Intacct_ID = qryACCOUNT_USER_CATEGORY2.Intacct_ID;

Screenshot attached