There is a table of film titles. The child table is all the roles of the people who worked on each film. When the user enters a role record in the portal, they choose a name from a popup list that is populated from a master list of all people who have previously been entered.

There is also a master people table, with each person listed once.

Question: how to enter a person's name in the join "role" table and have that new name also entered into the master people table?

thanks for your help! I feel I must have done this before but can't recall how!

Leah Brooks

so we would have:

many peolpe working on 1 film

and many films worked on by 1 person.

thats many to many and a big no no.

so we have:

films (1-many) roles (many-1) people

so each film may have many roles/'assignments'

each person may also have many assignments.

basically create those 3 tables.

you list of people, who are the drop down are in people.

then the roles/ assignments table is seperate

If you have an assignments portal on the films layout, then choosing a persons name (saves personID to assignments table).

.... u know what, im gunna make it for you.

as you can see we can now add people to films, or films to ppl.

hope this is what your after

...slight problem cant upload it for some reason. (fp7)

Edited February 23, 200718 yr by Guest

You need to compress the files to attach it such as .zip format.

so we would have:

many peolpe working on 1 film

and many films worked on by 1 person.

thats many to many and a big no no.

There's no problem using a many-to-many. The relational setup that you go on to describe IS a many-to-many, and is what Leah has already given.

Leah,

The process generally employed for adding records has the user create both parent records prior to creating the join. To go the other way around requires a scripted solution.

You could use a global (or set of globals) for adding the join, then if the other selected parent does not exist, add the parent based on the data entered in the global and grab the key to insert into the join.

Keep in mind that such a solution has the possibility of duplicate parents being generated because of differences in spelling or errors in data entry.

Thanks for all of the suggestions! I think I will force the users to add a new person if the name doesn't appear in the existing people list.