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.

 

[plog]

Hopefully you have an autonumber ID key in both tables (TeamID and ProcessID). If you don't--get one. When you do you create a third table called Assignments which will have these fields along with some sample data:

TeamID, AssignmentID, TimePercent
1, 1, .2
1, 3, .35
2, 4, .5
3, 2, 1
2, 5, .5

Then to assign tasks using a form you would create either a form based on the Team table or the Proccess data and then have a sub-form based on the Assignments table where you can make your assignments.

 

[brum_rich]

plog, thanks for the response although can you clarify a bit further? If I create the third table called assignments with the team ID and Process ID then how do I pull in the ID data from the Team & Process Data tables?

For example for each Team ID I need to be able to allocate one or more tasks from tbl Process and they apply the percentage. I'm still not quite sure how your solution would allow this.

Apologies but my Access skills are pretty rusty.

 

[plog]

You 'pull' the data you need into the Assigments table by creating input forms with drop downs on them. You might head over to the form section, or look for example databases for reports with sub-reports.