Auto update of records when fields change
I have a database that is a contacts database of sorts. The problem I am having is getting a particular field to update when I update a record on another table
There is a table called Courses and it has list of course names in it. Its just a plain text field. The Courses table links to a second table called Join. The Join table is basically an intermediate table in between the Courses table and People table to sort out a many to many relationship between Courses and People (another table). The records in the Join table are created from a portal field in the People table.
When the records are created in the Join table, there is a field for 'Course Name' in the Join table and this is a value list based on the 'Course Name' field in the Courses table. So when I create a record in the join table the record of the course name in the table is static.
What I want to do it change the way the Course Name field in the join table access the Courses table so when I change the name of a course in the courses table all the records in the join table are updated.
How can I do this?
Let me know if more information is required.
Thanks in advance.