Use an Update Query to Copy Fields from One Table to Another?

[warrenpage]

Hello, everyone.

I'm working on a movie database, and have imported an Excel spreadsheet with 497 records into a table.

The table is named tblMovies. That will serve as my parent table.

Two of the fields in tblMovies are named Actor Last Name, and Actor First Name. I want to copy these two fields, along with their entire contents, into a second table, named tblActors. These two tables will eventually be joined to a junction table, and I will be using the tblActors table in a subform.

Anyway, my question is simple: I am not 100% sure which is the easiest method to get those two fields from tblMovies into tblActors.

I've read an Access Knowledge Base article that recommends an Update Query.

http://support.microsoft.com/kb/209728

I admit I've never designed an Update Query before. Is this the best way to do this, or is there an easier method? According to the article, duplicate data such as this is not good database design. However, I plan on removing those two fields from the tblMovies table once they are shifted over into tblActors.

Any suggestions on whether or not I should do an Update Query? Since I've never done one before, can anyone recommend a beginners' step-by-step tutorial that demonstrates this? Thank you! Warren Page

[June7]

The design change you want to do is good idea. Congratulations on recognizing the need.

The technique described in article is not quite what you need because you need to take the many occurrences of each actor in tblMovies and generate a single record for each actor in tblActors. This is the reverse of what is happening in the article which is updating the many occurrences of a product in OrderDetails with the product name - actually a bad example.

Do you already have tblActors built? If not, can do a MAKE TABLE action with tblMovies as source. Review: http://office.microsoft.com/en-us/ac...010108505.aspx

First, need a SELECT query that will select distinct values for each actor and use that as the source for the new table:

SELECT DISTINCT [Actor Last Name], [Actor First Name] FROM tblMovies;

If you already have tblActors built, that does complicate a little.

There is a method to convert an Excel sheet to an Access database. Review http://databases.about.com/od/tutori...eltoaccess.htm


BTW, advise not to use spaces, special characters/punctuation (underscore is exception) in names.

[warrenpage]

Thanks for the reply. I have not created the tblActors table yet. I've been reading and watching tutorial videos before attempting any of this.

I did watch a video that mentioned a Make Table query, but the procedure wasn't demonstrated. So, I need to find material dealing with that. After I do, I'll experiment. From now on, I'll leave out spaces. Thanks for helping.

[June7]

Copy/paste records works, too.

Manually build tblActors. Select all the records in the DISTINCT query and copy/paste into tblActors.

[warrenpage]

This turned out to be too easy. I made a Make Table query, and it made a new table perfectly. The only thing I had to do was add a primary key field. No problem. It added the correct number of records, and they were all accurate.

I also made a separate table for directors. I had fields for directors' last and first names, so now there's a table for them. I'm not sure if I need to set up a relationship between movies and directors, or if I can just add a combo or list box to the parent tblMovies table. I'll dabble with that. I do know I'll have to create an expression to merge the directors' last and first names, so they appear like this: Kubrick, Stanley. I think I know how to do that, but I'll need to work on that. Anyway, I'm making progress. Thanks!

[June7]

What about people who are both directors and actors? Maybe you should have only one table for people.

Usually a movie has only one credited director, then there are the Wachowski sibs who always seem to work as a team and both get credit. Seems I've seen one or two others like that.

SELECT ID, LastName & ", " & FirstName As DirectorName FROM tblDirectors;

Same for actors.

[warrenpage]

Some movies in my collection have several directors, but that is not a critical enough issue for me to design the database to accommodate that criteria. I pick the director I consider to be the primary one, and that's it.

If it's brothers, then I treat them as one name, like: Coen Bros. It's a personal choice.

As for directors who are actors and vice versa (i.e. Ben Affleck and Argo), I don't care about that at all. A director is a director and an actor is an actor.

It might not be the best database design from a technical point of view, to have duplicate data, but I think there will be too few actor/directors and multiple director scenarios for me to merge all people into one table.

[June7]

I presume this is for personal use and not something being distributed and having multiple users. As long as you understand what you have built and know how to work with it, is good.

[warrenpage]

Yes, it is for my own use only. And to be quite frank, it's not your typical movie database. I won't get into the fine details about what it's exactly for, but it's nothing secret or anything like that. Anyway, thanks again for helping out.