D
Dmitry Akselrod
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:
Fields:
ProjectID
ProjectNumber
ProjectName
Table: Staff:
Fields:
StaffID
StaffName
StaffFunction
Capacity
StdRate
Rate1...Rate6
Table: Weeks
Fields:
WeekID
WeekDateRange
Table:StaffingItems
Fields:
StaffingItemID
ProjectID
StaffID
WeekID
Hours
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,
Dmitry
structure (there are other tables, that don't have anything to do with the
current problem):
Table: Projects:
Fields:
ProjectID
ProjectNumber
ProjectName
Table: Staff:
Fields:
StaffID
StaffName
StaffFunction
Capacity
StdRate
Rate1...Rate6
Table: Weeks
Fields:
WeekID
WeekDateRange
Table:StaffingItems
Fields:
StaffingItemID
ProjectID
StaffID
WeekID
Hours
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,
Dmitry