I am working on a database tracking job costs and future projected costs.
So my form has (and these are user defined so they will change, but I am using queries to update my table to that is OK):
Filter for multiple jobs
Beginning date (User Defined, will change) = 2004
Ending date (User Defined, will change = 2006
Inflation rate (User Defined, will change = 3%
My table has:
ID Job Labor Level Year Amount
1 New York Manager 2002 $42.50
1 New York Supervisor 2002 $23.75
1 New York Associate 2002 $15.25
2 California Director 2004 $75.22
2 California Manager 2004 $45.25
And I am trying to make my table have:
ID Job Labor Level Year Amount
1 New York Manager 2002 $42.50
1 New York Supervisor 2002 $23.75
1 New York Associate 2002 $15.25
1 New York Manager 2003 $43.77
1 New York Supervisor 2003 $24.46
1 New York Associate 2003 $15.70
1 New York Manager 2004 $45.08
1 New York Supervisor 2004 $25.19
1 New York Associate 2004 $16.17
1 New York Manager 2005 $46.43
1 New York Supervisor 2005 $25.94
1 New York Associate 2005 $16.65
1 New York Manager 2006 $47.82
1 New York Supervisor 2006 $26.71
1 New York Associate 2006 $17.14
2 California Director 2004 $75.22
2 California Manager 2004 $45.25
2 California Director 2005 $77.47
2 California Manager 2005 $46.60
2 California Director 2006 $79.79
2 California Manager 2006 $47.99
So basically I need a VBA code that will copy each record increasing the year and amount until the it reached the year specified in the End year on my form. Not all of these records are in the same year, and some may already have data for multiple years (in which case I only want the years that are not there and the increase in amount to calculate on the most recent year.
Any help would be appreciated.
So my form has (and these are user defined so they will change, but I am using queries to update my table to that is OK):
Filter for multiple jobs
Beginning date (User Defined, will change) = 2004
Ending date (User Defined, will change = 2006
Inflation rate (User Defined, will change = 3%
My table has:
ID Job Labor Level Year Amount
1 New York Manager 2002 $42.50
1 New York Supervisor 2002 $23.75
1 New York Associate 2002 $15.25
2 California Director 2004 $75.22
2 California Manager 2004 $45.25
And I am trying to make my table have:
ID Job Labor Level Year Amount
1 New York Manager 2002 $42.50
1 New York Supervisor 2002 $23.75
1 New York Associate 2002 $15.25
1 New York Manager 2003 $43.77
1 New York Supervisor 2003 $24.46
1 New York Associate 2003 $15.70
1 New York Manager 2004 $45.08
1 New York Supervisor 2004 $25.19
1 New York Associate 2004 $16.17
1 New York Manager 2005 $46.43
1 New York Supervisor 2005 $25.94
1 New York Associate 2005 $16.65
1 New York Manager 2006 $47.82
1 New York Supervisor 2006 $26.71
1 New York Associate 2006 $17.14
2 California Director 2004 $75.22
2 California Manager 2004 $45.25
2 California Director 2005 $77.47
2 California Manager 2005 $46.60
2 California Director 2006 $79.79
2 California Manager 2006 $47.99
So basically I need a VBA code that will copy each record increasing the year and amount until the it reached the year specified in the End year on my form. Not all of these records are in the same year, and some may already have data for multiple years (in which case I only want the years that are not there and the increase in amount to calculate on the most recent year.
Any help would be appreciated.