Rolling forecast query help.

[VT2000 via AccessMonster.com]

Hi everyone. I need some expert help.
I have a table with a current year, employee's name, job description and
his hourly wage. I need to create a report that forecasts each employee's
hourly wage for the next X number of years at a X percent of increase.

the final query/report need to show the info in this format.

2005
EMPLOYEE 1 $20
EMPLOYEE 2 $18
EMPLOYEE 3 $25

2006
EMPLOYEE 1 $22
EMPLOYEE 2 $20
EMPLOYEE 3 $27

2007
EMPLOYEE 1 $25
EMPLOYEE 2 $22
EMPLOYEE 3 $30

and so so...

please help.

How many years to set the forecast to and the percentage of increase need
to be a variable set by the user too.

 

[Guest]

Mybe it's not the best way but i would insert all the data into a temp table

dim mydb as database, myrec as recordset, MyTemp as recordset
docmd.runsql "delete * from Temp Table"
docmd.runsql Insert All records 0f MyTable to temp table where Year =
currYear"
set mydb =codedb
set MyTemp = mydb.openrecordset("select * from Temp Table")
For I = CurrYear+1 to Chosen Year
set myrec = mydb.openrecordset = (select * from temp table where Year =
I-1)
While not myrec .eof
MyTemp.addnew
MyTemp!Year=I
MyTemp!employee=omyrec!employee
MyTemp!wage = omyrec!wage * percent of increase
MyTemp.update
myrec.movenext
Next I
that will insert all the records, all you have noe is to build a report
based on that table, with grouping and sorting on the year and the rest of
the data in the details.

 

[Dale Fye]

How about a union query to get your data, then a report to format it the way
you want.

Select 2005 as FiscalYear, EmployeeID, Salary as FYSalary From yourTable
UNION ALL
SELECT 2006 as FiscalYear, EmployeeID, Salary * (1 + PercentIncrease) as
FYSalary FROM yourTable
UNION ALL
SELECT 2007 as FiscalYear, EmployeeID, Salary * (1 + PercentIncrease)^2 as
FYSalary From yourTable

HTH
Dale