Linking records in one table to multiple records in another and assign %

[brum_rich]

I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).

I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.

I suspect this is relativley straight forwards although my Access skills/knowledge is pretty rusty so I want to establish the best way to achieve this rather than fumble a clunky solution together.

Once I have established the best approach to this I will then need to create a form/interface to enable users to undertake this.

Any thoughts? Many thanks.

[meratigoerr]

One method. Create a new table. ID field (Primary Key), Foreign Key from team table, foriegn key from process table, and then any other fields you want related to the team and this particular project. You might want to consider not storing the percentage. Percentage is a calcuation and these normally shouldn't be saved in your table. Instead, consider having the employee enter amounts of time spent on project. Then you can calculate an updated and accuate percentage whenever you need to.

[brum_rich]

One method. Create a new table. ID field (Primary Key), Foreign Key from team table, foriegn key from process table, and then any other fields you want related to the team and this particular project. You might want to consider not storing the percentage. Percentage is a calcuation and these normally shouldn't be saved in your table. Instead, consider having the employee enter amounts of time spent on project. Then you can calculate an updated and accuate percentage whenever you need to.

Thanks for the response. Do you mean create a blank table with those fields? I assume that you mean create a table that will store the results of the relationship between team and activiity. For example I want to capture detail like process A (75%) and process B (25%) (from tbl Process) against team 1 (in tbl Team), and then process A (25%), process B (10%), process C (65%) and so on.

Therefore I need a way to select a team, allocate the tasks/activities & percentage and store to enable me to run queries & reports on. I'm not quite sure how this would work based on your response.

Any further advice would be much appreciated, thanks.

[brum_rich]

The above post was meant to read..

Thanks for the response. Do you mean create a blank table with those fields? I assume that you mean create a table that will store the results of the relationship between team and activiity. For example I want to capture detail like process A (75%) and process B (25%) (from tbl Process) against team 1 (in tbl Team), and then process A (25%), process B (10%), process C (65%) to team 2, and so on.

Therefore I need a way to select a team, allocate the tasks/activities & percentage and store to enable me to run queries & reports on. I'm not quite sure how this would work based on your response.

Any further advice would be much appreciated, thanks.