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

[tatihulot]

Hi, I would like to request some assistance with my database, please.



I have a movie collection database that I’ve imported movie titles into. I’ve attached the database. If you open the Movie table, and you scroll down to the bottom of the Title list, after the films by director Frederick Wiseman, you’ll see 43 movie titles.

The Actor table is linked to the Movie table via a join table named Actor_Join. Right now, the only actor I’ve added to the Actor table is Harold Lloyd.

I’d like to batch add the actor Harold Lloyd to those 43 movie titles so his name appears in the Combo Box in the Actor subform tab of the corresponding title. I haven’t figured out how to do that. The only way I currently know how is to add them via the form one by one. That’s impractical, of course. I’m going to want to do this also with all my Charlie Chaplin, Buster Keaton, Clint Eastwood, Laurel and Hardy, and Little Rascals movies--eventually.

Is this possible? If it is, what is the most practical way of adding an actor’s name to those movies? Am I correct in assuming this requires an Append Query? If it does, I’m not really sure how to go about designing one.

Thank you very much.

actors.zip

[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.

[Micron]

If the 43 titles are after id 66552 then your update WHERE clause can be "...WHERE MoviePK > 66551". Usually, this sort of thing is done via a form, where you enter all the movie details at the time of movie record creation.

FWIW, the Watched field is redundant. If there is a date in the next field, it was watched. If not, then it hasn't been watched.

[tatihulot]

Then change to an Update query and select the ID of that actor.

I successfully created the simple query. Could you elaborate just a bit more on how to select the actor's ID? Where in the query do I do that? Thank you.

[tatihulot]

FWIW, the Watched field is redundant. If there is a date in the next field, it was watched. If not, then it hasn't been watched.

I didn't think about that at all. Thank you for bringing this to my attention.

[Micron]

First, create a backup copy of your actor join table, then paste this into a new query

UPDATE Movie LEFTJOIN Actor_Join ON Movie.MoviePK = Actor_Join.MovieFK SET Actor_Join.ActorFK =20, Actor_Join.MovieFK = [movie].[MoviePK]
WHERE (((Movie.MoviePK)>66551));

Giving you the sql is far easier than writing step by step instructions, and when you look at it in design view, you'll be able to see what I could have written out in detail. Study the design and see if you have questions.

EDIT - part of that review should be that you double click on the join line and understand the option chosen.

[Welshgasman]

Then change to an Update query and select the ID of that actor.

I successfully created the simple query. Could you elaborate just a bit more on how to select the actor's ID? Where in the query do I do that? Thank you.

You change that query to an update query. In design view you set the required field to your required value, which is the autonumber value from the Actor table.
Access will warn you that you are updating 43 records, then confirm.

Perhaps make a copy of the table first in case you do not get it correct first time. This is why I always do a Select first, to see if the records are correct for being updated.

[Gicu]

I think you need a new form with a listbox or subform control listing the movies in which you could select the ones you want to assign the actor to. Then you have a combo (with its row source being the Actor table) where you select the actor. And finally a button that adds the records to the Actor_Join table, either in a loop if using a listbox or an append query if using a subform. To use a subform you will need to add a new Yes\No field to the Movie table (or better to use a temporary table that has one IsSelected and empty it and populate it every time from the Movie table you will do this task). Your append query will then use to IsSelected field to identify the MoviePK to be added to the Actor_Join table and take the ActorPK from the combo.

Cheers,

[tatihulot]

Giving you the sql is far easier than writing step by step instructions, and when you look at it in design view, you'll be able to see what I could have written out in detail. Study the design and see if you have questions.

EDIT - part of that review should be that you double click on the join line and understand the option chosen.

When I try to run that SQL code I get a dialog box that says Syntax Error in Update Statement.

[Micron]

worked for me. Best to post your attempt so anyone can see if there's a mistake in the copy/paste.

Actor_JoinPK	MovieFK	ActorFK
6	66552	20
7	66553	20
8	66554	20
9	66555	20
10	66556	20
11	66557	20
12	66558	20
13	66559	20
14	66560	20
15	66561	20
16	66562	20
17	66563	20
18	66564	20
19	66565	20
20	66566	20
21	66567	20
22	66568	20
23	66569	20
24	66570	20
25	66571	20
26	66572	20
27	66573	20
28	66574	20
29	66575	20
30	66576	20
31	66577	20
32	66578	20
33	66579	20
34	66580	20
35	66581	20
36	66582	20
37	66583	20
38	66584	20
39	66585	20
40	66586	20
41	66587	20
42	66588	20
43	66589	20
44	66590	20
45	66591	20
46	66592	20
47	66593	20
48	66594	20

[CJ_London]

Looks like you are missing a space
LEFTJOIN

[Gicu]

Please have a look at frmAddActor in the attached file.
Cheers,

[tatihulot]

Looks like you are missing a space
LEFTJOIN

Yep. Fixed it, ran the query, works fine.

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.

Thank you, everybody!

[Micron]

Thanks CJ. That is not what I copied, so forum must have removed the space for some reason.

[tatihulot]

Here is the revised database with the update query included:

revised.zip