Database Design: Normalize daily scheduled time

[Lorlai]

I have a monthly forecast for about 50 employees that I have stored into a table in access. My client would like an interactive form that will allow a user to update the forecast on a daily basis. Currently, the monthly forecast is stored as total hours forecasted for each employees, aggregated by month. I am trying to find a way that will allow me to separate these hours into a daily work day. In general, an employee will work 8 days Mon-Fri, with no hours worked on Saturday or Sunday.

The current table I have imported from the monthly forecast spreadsheet looks like:

Forecast_TABLE
Emp_ID
Emp_Name
Month
Year
Scheduled Work

Emp_ID	Emp_Name	Month	Year	Scheduled Work
12345	Joe Jon	07	2013	176
67890	Dennis Rosmith	07	2013	168

The above examples demonstrate what the data will typically look like. In general, all employees will work 8 hours every work day in a month. The exception to this is vacation/holiday time. Employee Joe Jon worked 8 hours every work day (Monday - Friday) of the month of July (except for the holiday of July 4), for a total of 176 hours. Dennis Rosmith took one day of PTO (paid time off) after the July 4th holiday, and worked 8 hours every work day except for his PTO day. As such, Dennis Rosmith's total hours for July were 168.



My client requires that the information be displayed in a spreadsheet form in Access, where each cell is rewritable. They also require the ability to update the scheduled work on a daily basis. A rough draft of this requirements looks like the example below:

Emp_ID	Emp_Name	Sunday 6/30/13	Monday 7/1/13	Tuesday 7/2/13	Wednesday 7/3/13	Thursday 7/4/13	Friday 7/5/13	Saturday 7/6/13	Sunday 7/7/13	Monday 7/8/13	Tuesday 7/9/13	Wednesday 7/10/13	Thursday 7/11/13	Friday 7/12/13	Saturday 7/13/13	Sunday 7/14/13	Monday 7/15/13	Tuesday 7/16/13	Wednesday 7/17/13	Thursday 7/18/13	Friday 7/19/13	Saturday 7/20/13
12345	Joe Jon	0	8	8	8	0	8	0	0	8	8	8	8	8	0	0	8	8	8	8	8	0
67890	Dennis Rosmith	0	8	8	8	0	0	0	0	8	8	8	8	8	0	0	8	8	8	8	8	0

In the example above, 0 indicates no scheduled hours (or no hours that will be charged to the company) and the 8 indicates the amount of hours scheduled for the day.
Is there a way to take the monthly total of hours worked, and use that total to display the scheduled hours in the daily format above? Or is there a way to better design the database for this information?

Thank you for any help you may provide!

[June7]

This would require elaborate VBA code.

You might find this of interest https://www.accessforums.net/databas...ses-18459.html

Duplicate thread on same topic is against forum guidelines. I have deleted other thread.