Update Query

[rangersedge]

So I have a table with a list of actors and roles that could be over 10 records long. Then I have a table for movie titles. I want to copy the first 5 records from the actors table into the movies table. The problem is the movie table has them broke out into seperate fields such as...

Actor1 Role1
Actor2 Role2
Actor3 Role3
Actor4 Role4
Actor5 Role5

How do I update these into the proper field?

 

[BlueIshDan]

You're going to want a separate table. Linking actors to movies.

Movies: Movie_ID, name, date, etc.
Talent: Talent_ID, name, gender, date_of_birth, etc.
Roles: Movie_ID, Talent_ID, role, number_of_lines, etc.

I believe this is called a junction table.

 

[rangersedge]

I thought about that but I already have over 400 records set up the other way. I did figure it out using a rather complex multiple query and vba combo. I will post the process later.

 

[BlueIshDan]

You could write queries to transfer them to the new design.

 

[BlueIshDan]

(TIP) Fix errors in design when they come or they will cost you more time(money) to scale with the time your project exists.

 

[rangersedge]

I think it would be easier to transfer them to a new table. I will look into that.