Database Relations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Philippe Perrault - 12 Sep 2007 15:08 GMT

I have table with a list of tasks and a second table with a list of people
and I want to be able to track who needs to do what tasks but each person has
to complete more then one task. How do I set it up so each person is related
to multiple tasks?

Reply to this Message

scubadiver - 12 Sep 2007 15:48 GMT

Each task can have many people and each person can perform many tasks?

This is a many to many relationship

PK = Primary Key

tble_task
taskID (PK)

tble_junction
taskID (FK)
PersonID (FK)

tble_person
PersonID (PK)
PerName

Go to tools -> relationships.

Show the three tables, drag the primary key to the foreign keys and
establish referential integrity.

1) Put the task in the main form
2) Put the PersonID foreign key field from the junction table in the subform
as a combo box. BUT USE "tble_person" AS THE SOURCE FOR THE COMBO.

When you set up this combo

1) In the FORMAT tab

column count: 2
column widths: 0cm;3cm

2) In the DATA tab

bound column: 1

This will store the person's ID number but show the name.

Let me know

Signature

"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

> I have table with a list of tasks and a second table with a list of people
> and I want to be able to track who needs to do what tasks but each person has
> to complete more then one task. How do I set it up so each person is related
> to multiple tasks?

Reply to this Message

Philippe Perrault - 12 Sep 2007 18:20 GMT

This is my first time trying to use a many to many relationship.
What data type do I use for the foreign keys? I can only use one Auto
number per table. Is only one an auto number or are they both just numbers?

When I am building the record source for the forms what tables do I put into
the query? Just the person table and junction table? or include the task
table as well

> Each task can have many people and each person can perform many tasks?
>
[quoted text clipped - 41 lines]
> > to complete more then one task. How do I set it up so each person is related
> > to multiple tasks?

Reply to this Message