Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: Design Logic Help

    I am designing a staffing per project access database. I have the following structure (there are other tables, that don't have anything to do with the current problem):

    Table: Projects:

    Table: Staff:

    Table: Weeks


    Relationships: These are all one to many relationships with referential integrity maintained. The StaffingItems is always on the many end.

    Projects.ProjectID --> StaffingItems.ProjectID
    Staff.StaffID --> StaffingItems.StaffID
    Weeks.WeekID --> StaffingItems.WeekID

    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:

    Project: ProjectNo - ProjectName

    Staff HrsWeek1 HrsWeek2 HrsWeek3 HrsWeek4....HrsWeek17
    Staff1 30 36 0 42 4
    Staff2 40 40 20 8 32

    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?

    Thank you in advance,

  2. #2
    Join Date
    Nov 2003
    Here is one option:
    Look at the attached bmp

    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.

    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts