DB Design Conundrum

[wchernock]

I have a DB Table with a dual key of ProjectName and AssignedResource. I have built a form based on the table that shows me all of the projects a given resource is assigned to. I would like to create an HoursForecasting table but am unsure of how to structure it.

Ideally I would like to show a list of all of the projects a resource is on and give them fields to fill in on a weekly basis for next weeks, 2weeks and 3 weeks out forecast.

I have tried to create a table with Projectname, Resource and Week# as the keys and nextweek, 2week and 3week as the repeating data. The problem is that the table is not populated, so when I open a form to inut the data nothing exists.

I could prebuild the HoursForecast Table everytime a project or resource is added, but I am wondering if my overall design of the tables could/should be improved?

 

[KeithG]

I would create a table with four fields (ProjectName, AssignedResource, StartOFWeek, TimeAllocation). Use the Projectname, AssignedResource, and StartOfWeek as the PrimaryKey

 

[wchernock]

That is basically what I had originally done. The problem is that from my project list form (a filtered list of all projects for a given employee), I would like to have a hoursforecast button. This would open another form based on the table you described above. The problem is that the form is blank because each new week, a record has yet to be entered for the table.

Here is a simplified example:

ProjectAssignment Table
Project Resource Service Assignedby
proj1 emp1 1 mgr1
proj1 emp2 2 mgr2
proj2 emp1 1 mgr1

HoursForecast Table
Project Resource Week Forecast
proj1 emp1 1 20
proj2 emp1 1 20


So if I have a form that displays the projects for emp1 they see
Project Servce Assignedby
proj1 1 mgr1
proj1 2 mgr2
proj2 1 mgr1

If there is a button for HoursForecast, I would like it to default to current week (in this case week 2)
The form would query the table and find no records to display)

Seems like I either need to prebuild the HoursForecast Table so I can display the record with forecasts set to zero or design this differently. If there was only one project I can populate the HoursForecast record from the record selected on the projectlist, but I was hoping to do it more like a timecard.

 

[KeithG]

Why not just have the user create the record when the open the form and it is blank?

 

[wchernock]

Once again, if this were just a single record on the form, I could take that route. The idea is the the form is a hours forecasting across an employees project list (multiple records) Think of it as planning hours for all of next weeks projects.

 

[KeithG]

Use either a continous form or a form in datasheet view to display/create multiple records.

 

[The_Doc_Man]

I'm going on a tangent. Search this forum for the topic of "Scheduling" and "Booking" to see issues in time-based advanced forecasting.

What you are describing is that you want to "book" this resource's time. In an off-the-wall sense, this is like making an appointment with the resource - and the thing that keeps the appointment isn't a person but a project.

If you look at it in that light, a forum seach might be fruitful. Beware of the topic of scheduling, though, because it has TWO meanings - the other being "How do I schedule a future event to be performed by/on/for Access?" If it appears that the thread you are reading has gone down that path, it is not relevant to your question. The BOOKING threads, on the other hand, will be relevant - but that name isn't used by all such threads.