Displaying 2 dependent entities on form

[lloydmav]

I have a database and have to develop a form which displays information about Action Plans and there tasks, however after playing around for days with listboxes and subforms I can't seem to get them to work. The tables (Action Plans) and (Tasks) aren't directly related. Here's how they are related;

Action Plans>--ChangeType--<TypeTask>--Tasks

These are the Primary and Foreign Keys;

Action Plans(*Action Plans ID,Change Type)
ChangeType(*Change_Type_ID)
TypeTask(*Change Type_ID,*Task ID)
Tasks(*Task ID)

The problem is that there are no related fields between action plans and tasks.
Can anyone think of a way to have Action Plans and Tasks displayed on the form so that when you select a Action Plan, all the tasks related to that action plan are then displayed?

I think that the problem may lie in the way I have structured the relationships but is there any way round this with queries?

 

[ColinEssex]

Hi

I reckon you need to have a link of some description.

If you have an ActionPlan - it can have many tasks likewise if you have another ActionPlan - it can have many (different) tasks. Therefore you will need to differentiate between all the tasks depending on your ActionPlan.

The simple way to do it is to create a field in the Tasks table called ActionPlan and then enter each ActionPlan and the tasks.

ActionPlan. . . . . Task

A.........................DoThis
A.........................DoThat
A.........................DoAnother
B.........................DoThis
B.........................DoSomethingElse

You can then link them on the ActionPlan field in both tables.

Col

 

[lloydmav]

Thanks for the reply

I've already thought of that one and lots of other combinations, the problem is that the tasks for an action plan depend on a the change type of the action plan.
The system works so that a request in the system is created. when a request is made a change type is selected, this is then approved by a manager, it is then approved by a customer once all this has been done, an Action Plan is created. The Change Type is selected when a request is made. This is then carried through to the action plan record. Each change type should have a list of tasks that go with that type of change. So the user will not need to enter the tasks. So what I need is to display the tasks which are related to a change type which is allocated to an action plan.

Complicated I Know!

Any Ideas

 

[ColinEssex]

How many change types are there? and how many action plans are there?

Col

 

[lloydmav]

The number of action plans depends on the number of requests. The request is turned into an action plan onece it has been accepted. However there are about 30 Change Types and about 100 Tasks. More than one change type can share a task aswell!