I start with a Project. I assign Staff to the Project. For each staff member, I input the total number of hours they are scheduled to work on the project for each week, 17 weeks at a time. This is a rolling date database, where I deal with 17 weeks of data at a time. But I also want to maintain historical data. I also need to be able to start with a Staff Member, and assign a Project to the staff member and then input the amount of hours for each of the 17 weeks. So my core table is the StaffingItems table, and I need to maintain the relationship to Projects and Staff.
I need design 2 forms in Access that will allow me to edit this data, the following way:
If my table transfers, I basically have a Project From, with a subform in a DataSheet fromat that would list the Staff (with some other info) and how many hours this staff member is scheduled to work for each of the 17 weeks I am currently dealing with.
I am not sure how to design this form. I can't figure out how to display this information, such as that I have a main form that display the Project Information and a subform that has the Staff Members in the Rows and Number of Hours each Week in Columns.
Also, is my database structure correct? Do I have the correct tables and relationships for this type of a rolling date database?
I would set it up so that you can the steffer in question and you select the project. If you have never assigned this project to this staffer you would build the rest of the form You would change the labels.cpation to match what it need to say based on this week and then update you table after all the weeks have been update with information.
If you have assigned this project to this staffer and are you continuing to add hours to more weeks, when you select the project you would update the labels and bring back the current information coorespnding to those labels and the update the information based on the additional hours you add or change.