How Do I "Batch Add" Records into a Join Table That Will Link to Another Table?

[Gicu]

Using the update query will only allow you one actor per movie....

[Micron]

I assume if I want to use this SQL for other actors, all I have to do is make sure those movies are at the end of the list, change the ID number in the SQL range code, and then change the Actor ID. I'll try it.

You could, or if there are not too many updates to do at once, then you might want to try an IN clause: WHERE Movie.MoviePK IN(66552, 66557, 665560, 665565)
As I first said, you shouldn't be having this issue because this should be taken care of via forms at the point of movie record creation. If you need to edit the related record and add actors, your form should take care of that. Not only that, so far, your join table is being used to relate one actor to many movies when in reality, many movies also have many actors. If you have to do this for 5, 6, whatever, more actors for the same movie that's going to get interesting.

[Gicu]

frmAddActor in the file I uploaded would allow you to filter by director, country, etc. and\or manually select movies in which the selected actor has played. It will then append the records to the Actor_Join table so you can have multiple actors per movie (not saying the one-actor movies are boring but ...).
Cheers,

[madpiet]

bulk-adding like you're describing might not be a great idea... I'm not sure how to do it where it's easy to select the movies that would make it any more efficient than entering the movies from a combobox in a subform. Since there are LOTS of Actors and LOTS of Movies, selecting which actors appear in which movies isn't going to be automatable (is that even a word?) the way I think you want to.

I think the best you can do is to have a list of movie titles (well, you might need something like [Movie Title] & " (" & [Release Year] & ")" so you can see which release of the movie you're selecting.). I would use a combobox in the subform to show either the Movies or Actors and then just make the primary key of the "AppearsIn" table be (MovieID, ActorID). Say you are in the Movies main form and have an "Acts In" subform. You'd just have a combobox in the subform to select the actors. (Save ActorID, but show Actor name in the combobox).

[Welshgasman]

You use an update query. Not an Append query, that adds extra records.
First create a select query to get and check the records that should be updated.
Then change to an Update query and select the ID of that actor.

However after further thought, you would likely need a junction table to link Actors with Movies, so yes, that would then be an Append query with the relevant PKs of the other tables.

[tatihulot]

Placeholder, please ignore this post.

[Welshgasman]

That is because it is an Update query and Updates the fields. The clue is in the name.

As I came back and stated, if you have an ActorMovie junction table, then you would be appending records.

[Gicu]

Or if you would have seen my post #16....

[madpiet]

First off, since many actors can perform in many movies, and many movies can have at least some of the same actors, you need a bridge table between the Movies table and the Actors table. The table would be like this:

CREATE TABLE MovieActor (MovieID INT, ActorID INT PRIMARY KEY (MovieID, ActorID)
FOREIGN KEY MovieID REFERENCES Movie(MovieID), FOREIGN KEY ActorID REFERENCES Actor(ActorID) )

The bridge table is the one you would use to base the subform on. So the whole "modify Movie table" part is a red herring. If you wanted to do something like show a comma-separated list of actors in a movie, you'd use a query for that anyway and base the form on that.

[Gicu]

He already has that table (called Actor_Join) as described even in the post's title...

[tatihulot]

Fixed the Update query to append actors into the Actor_Join field.

INSERT INTO Actor_Join (ActorFK, MovieFK)
SELECT 16, Movie.MoviePK
FROM Movie
WHERE Movie.MoviePK BETWEEN 1875 AND 1884;

[Welshgasman]

No longer an Update query. Get the terminology correct.

[Gicu]

Did you look at the file I gave you in post #12? What is wrong with the frmAddActor? You can even modify that to allow you to add batches of genre and location to their respective join tables for the selected movies. And no messing around with the append query hardcoding values...
To each their own I guess.....

[tatihulot]

Did you look at the file I gave you in post #12? What is wrong with the frmAddActor? You can even modify that to allow you to add batches of genre and location to their respective join tables for the selected movies. And no messing around with the append query hardcoding values...
To each their own I guess.....

My apologies. I'll give it a closer look.