I might be making more of this than it really is but here goes. I have a quote db that accumulates quote information. Since traditionally we get x% of our bids, this tool should provide some useful information. A quote will have a duration, meaning, if we get the deal, how long will we be selling this part to them. The duration is expressed in years. I also no the projected start date, so with the duration and start date I can calculate projected end date of revenue stream. I have broken down the projected revenue stream into revenue per month, so I can calculate the projected revenue for say, 2002 if we start part way through the year, and I know what the revenue stream is for each subsequent year. All that is done. My problem is I need to accumulate all the 2002, 2003, 2004, 2005 quotes by year. It would be no problem if all quotes had projected start dates in 2002 and end dates in 2004 but that's not the case obviously. Year 1 might be 2002 for one quote and 2003 or 2000 for another quote. Here a condensed version of what my query looks like now.
Start Year StartYearRevenue Year1 Year1Revenue etc.
2001 $18254.00 2002 25,254
2003 15,235 2004 17,354
2000 8,245 2001 96,546
I created a 2001 and 2002 and 2003 field and tried to use IIF to drop the appropriate revenue into the corresponding column, however, it's too complex to run.Is there a better way to accumulate each years revenue? Anybody? Anybody?
Start Year StartYearRevenue Year1 Year1Revenue etc.
2001 $18254.00 2002 25,254
2003 15,235 2004 17,354
2000 8,245 2001 96,546
I created a 2001 and 2002 and 2003 field and tried to use IIF to drop the appropriate revenue into the corresponding column, however, it's too complex to run.Is there a better way to accumulate each years revenue? Anybody? Anybody?
