Entering Complex Data into Access

Fairly new to Access and trying to do something that seems simple, but may be very complex. I want to create a database of projects, each project has several phases (ie pr开发者_开发知识库oposal, marketing, etc) and that will allow for multiple employees to work on a single project. Ie Bob and John are working on project number 102. From here, i would like to enter the forecasted start and end dates for each phase of the project, and enter the forecasted number our hours each employee will be allowed to work on that phase of that project ie.

Project - Employee - Phase - Start - End - (list weeks)

102 - Bob - Marketing - 12-May-10 - 21-May-10 - 3 - 5 (3 hours first week, 5 hours the second)

and so on

Basically would all this data be on one table, or several? And can access dynamically show the weeks between the start and end date so that i can input the hours?

I feel this database will become severely complicated :S

Thanks, J

Perhaps some more details will help. Here is a very rough outline of the type of thing you may want. As you consider, you will see the need for additional tables.

1. Projects
ProjectID
Details

2. Employees
EmployeeID
Details

3. Phases 
For this table, you may wish to use a unique phase ID 
or a combined key created from project and either a look-up table 
numeric ID or a description of the phase. Both have their advantages
PhaseID 
ProjectID 
PhaseDescription             
Details 

4. EmployeePhases
Depending on your decision above, either
PhaseID          )
EmployeeID       ) Unique key
Start Date Time  )
End Date Time

or

ProjectID         )
PhaseDescription  ) Unique Key
EmployeeID        )
Start Date Time   )
End Date Time

It can get a little complicated getting the right data out, but I think you will find that sticking fairly closely to the rules for normalization will allow you to get the data you need, and the data you do not yet know you need, with the least trouble.

Whether or not you use Access, you should know what normalization is and why it's important.

It depends on what "severely complicated" means to you. A dozen tables is hardly mind-bending.

one project, that has multiple phases, to multiple employees where id like to track the hours for each employee for each phase

You need some join tables. It sounds like you have several many-to-many relationships here:

• employee is many-to-many with phase
• project is many-to-many with phase

The employee-phase many-to-many table might be named time_entry, because the hours you enter really need to be associated with both an employee and a phase.

The pop out calendar will be good for entering the start and end dates. But then where does the hours for each empoloyee go?

I've got an Excel file set up for this that is huge, slow and has a horrible user interface (one that the other guys here would struggle to use).

I still think Excel isn't right for this task, and Access is the way to go.

In Excel I have a tab for each employee (so adding/editing info is a pain), and each employee has a list of jobs (jobs are repeated for multi-phase projects) and then a list of dates with hours entered. Using may equal statements, the file works, but poorly and doesn't give the information we want without a lot of manual work.