koolwaters
Active Member
- Joined
- May 16, 2007
- Messages
- 403
Hi Everyone!
I need to create a budget forecast report in Access and need some assistance.
The company provides tuition assistance to all employees once they meet certain requirements. The assistance can be for a diploma, associates degree, bachelors, masters, etc. Once the application for assistance is approved, the employee is refunded any money spent on a yearly basis after the necessay receipts have been submitted.
Scenario:
An employee is approved to receive assistance for a bachelor's which is supposed to be completed in 4 years. The start date is September 2010 indicating that the degree should be completed in 2014. Let's say the employee will receive $10 per year for the duration of the program, which means they will receive $40 in total.
The employee will receive $10 in September 2011, 2012, 2013 and 2014. In submitting the budget forecast, for 2011, I need to budget for the $10 to be paid out that year. So for all approved applications, my forecast needs to show a summary of assistance to be received, grouped by the respective department of the employee.
I have a table in my database that captures the application information for the assistance. Among others, there is an ApplicationID (PK), ProgramID, DisciplineID, EmployeeID, StartDate, EndDate, AmountApproved. I also have a StaffList table and a Department tables in the database.
Looking at the AmountApproved, StartDate and EndDate, I need to be able to forecast the budget needed yearly for these applications. There are also instances where I have to provide a forecast for more than one year so this needs to be a factor.
I need some guidance in creating the query to provide this information. I hope that I have been explicit.
Thanks for any feedback provided.
I need to create a budget forecast report in Access and need some assistance.
The company provides tuition assistance to all employees once they meet certain requirements. The assistance can be for a diploma, associates degree, bachelors, masters, etc. Once the application for assistance is approved, the employee is refunded any money spent on a yearly basis after the necessay receipts have been submitted.
Scenario:
An employee is approved to receive assistance for a bachelor's which is supposed to be completed in 4 years. The start date is September 2010 indicating that the degree should be completed in 2014. Let's say the employee will receive $10 per year for the duration of the program, which means they will receive $40 in total.
The employee will receive $10 in September 2011, 2012, 2013 and 2014. In submitting the budget forecast, for 2011, I need to budget for the $10 to be paid out that year. So for all approved applications, my forecast needs to show a summary of assistance to be received, grouped by the respective department of the employee.
I have a table in my database that captures the application information for the assistance. Among others, there is an ApplicationID (PK), ProgramID, DisciplineID, EmployeeID, StartDate, EndDate, AmountApproved. I also have a StaffList table and a Department tables in the database.
Looking at the AmountApproved, StartDate and EndDate, I need to be able to forecast the budget needed yearly for these applications. There are also instances where I have to provide a forecast for more than one year so this needs to be a factor.
I need some guidance in creating the query to provide this information. I hope that I have been explicit.
Thanks for any feedback provided.
