Project Management: ID, Component, Task and Hours.

[roneyrod]

I have several projects; each of them has a different number of components (or areas of expertise); each component has a different number of tasks, and each task will take a certain amount de hours to be completed. I need to create a database to manage these info. I thought of creating a table with the project id's as the primary key, and fields 01 through 10 to receive the components each project has. Then a second table would have as primary key a field named "projectcomponent" to hold the project id + component code, and fields "a" through "m" to keep the task descriptions. For example, project A23 has four components: Marketing (01), Data Collection (02), Data Analysis (03), and Project Management (04). I want A2301, A2302, A2303 and A2304 to appear as entries on the second table, in the primary key, so I can add the tasks for each component as fields. For example for A2301 (which is the marketing component of project A23) I can add meetings in field "a", budget analysis (b), and proposal preparation (c). Then on a third table, I want as primary key a field named "task" that would have as entries the project id + component code + task code (for example, A2301a), so I can add fields with dates, and fill them with the hours worked on each project, component and task. Then I need weekly reports with the updated total hours spent on each task.



So basically I need to automatically concatenate primary key and field names into one, and add it to another table. Is it something Access can easily do? Is there a smarter way to meet my needs? Needless to say, I know very little of Access. Many thanks in advance!!

[Ted C]

Sounds like you need a table of projects that has columns for information that is unique to each project. Then you need a second table of project tasks with the sort of information that is specific to tasks (such as the number of hours required), and one of its columns would relate it to the primary key of the projects table, so you had a link between each task and its associated project.

[roneyrod]

Sounds like you need a table of projects that has columns for information that is unique to each project. Then you need a second table of project tasks with the sort of information that is specific to tasks (such as the number of hours required), and one of its columns would relate it to the primary key of the projects table, so you had a link between each task and its associated project.

Well, I'm not sure my thread was clear enough. I've uploaded an Excel file with several spreadsheets, representing the different tables and forms. It can be downloaded from http://www.wikifortio.com/756709/PrjManagement.xls.zip (there is one xls file and one xlsx file). It's just a hypothetical situation, with only two projects... I would like to be able to use Access to perform the following workflow:

1. The first spreadsheet, "Structure" should be a form to enter project data;

2. "Component" is a table that should get filled from the form above, generating Component Codes;

3. Likewise, "Tasks" is filled by Component Codes generated from "Component", and should generate Task Codes;

4. One table per employee (E01, E02, E03, etc) is generated to hold data from their weekly timesheets (something similar to the spreadsheet "Timesheet"), and to calculate current costs associated with time spent on a project, task by task.

5. PrjHour is a table that updates hours task by task. It is fed by the sum of hours worked by all employees on each task (from E01, E02, etc).

6. Finally, hours and costs are placed back into "Structure" to help monitor evolution of costs for each project, weekly (there could be a table showing accumulative totals per week as well).

This is what I need. I can do the data entry described in "Structure" and "Timesheet". And I need the outcomes described in items 4 through 6. Can Access do it?

Many thanks in advance!!

Roney