How to Enter Many Actors into a Many-to-Many Movie Database?

[warrenpage]

My name is Warren, and I am using Access 2013. Could I get help designing a database? Thank you.

I've never created a relational database before, but I've read about them, and have watched a few videos on You Tube. I want to design my own movie collection database. I know there are many such databases available, but I figured this would be a good way to learn how to do this.

From what I've read and seen, the relationship between movies and actors is a many-to-many relationship. One movie can have many actors, and one actor can be in many movies.

I'm not sure where to put some fields. Also, I'm not sure how to enter multiple actors for one movie. So, I'd like help with this.

Let me show you my 3 tables:

1) Movies


Movie ID (Primary Key)
Title
Director
Date & Time
(I have some other fields here, but they don't need to be listed.)

2) Actors
Actor ID
Actor Last Name
Actor First Name
Actor Full Name (I'll use an equation or something to put the two fields together)

3) Relationship
Movie ID
Actor ID

The joined fields:
Movie/Movie ID >>>Relationship/Movie ID
Actors/Actor ID >>>Relationship/Actor ID

This is as far as I've gotten. I'm lost at this point, because I don't know how to design the forms so that I can enter all of the actors for a Movie record, if that makes sense, and I'm not sure how to display all of the actors.

I am assuming I need a separate form for the actors. Am I right? If I am right, then how do I create forms that will know when I am done entering data for a particular movie? I mean, if I am entering data for The Avengers, and I want to enter the names of five actors, how will Access know I'm done entering the data for that movie?

Did I create the tables and relationships correctly? Where do I go from here?

Thanks much from Warren Page.

[Dal Jeanis]

Pretty good for a first start. You might want to make your Relationship records a little more generic by adding a field to specify what kind of relationship the performer has to the movie. For example, the same person might be an actor in one movie and a director in others (or in the same movie).

So, First pass:

You have to have a form to enter your actors, and a form to enter your movies. These can be very simple forms.

Go ahead and make the actor forms and enter a few actors.

Next, make the movie form, and enter a few movies.

Third, create a form-subform arrangement. The form would have the same setup as the movie form, and the subform would be a continuous form based on a query against the relationship table. At the beginning, this subform will have no data, of course.

The subform would have a combobox where you can choose an actor, and another where you can choose a relationship. Enter a few actor records for each movie.

There's your trivial solution to the question you asked.

In successive versions, you can create a form-subform version of the actor form where you can add relationship records, a feature where you can add a new actor from the movie form, a feature where you can add a new movie from the actor form, and so on.

Keep iterating, fixing, improving.

Oh, and MOST IMPORTANT - back up early and often.

[warrenpage]

A sub-form...I've never used that feature. I'll have to dabble with it. I'm sure I'll have more questions, but this is a good start. Thanks.

I'm a little leery about a combo box, though, because I have a LOT of movies in my collection, and there will be a LOT of actors. It would be a very, very large combo box.

What I'd prefer--if possible--is to type in the names of the actors, and have some sort of auto-fill. Can that be done instead of a combo box?

[Dal Jeanis]

A combo box / listbox can autofill for you, yes.

If you have a huge number of items, you can use a textbox/listbox combination, where you put a few characters into the text box, and it autofills the matching records into the listbox/combobox.

If you haven't done subforms yet, I'd suggest that you start with the trivial solution, and work up from there.

Or, another strategy is to find a free template file out there (there are many) that does something similar to what you are looking for, and adapt it, or steal pieces, until you have what you want.

I'd suggest you do the easy method - learn one thing at a time, and make your database more robust as you learn.

Oh, one other strategy that might help you think about the whole process. Most of your actor and movie data could be input using an excel spreadsheet and the import function. You don't have to use access to initially input everything, unless it's really working for you.

[warrenpage]

I have not forgotten about this thread, and will be beginning to work on this database this week.

I don't plan on using Excel to input the data and import it into Access. However, I do plan on exporting the data to an Excel spreadsheet at some point in time! The reason I wouldn't input and import is that my eyes and brain would go bonkers after staring at a spreadsheet for a long time. I think an Access form would be easier. I've worked with Lotus Approach years ago, and FileMaker Pro, so I do have some experience with databases. Unfortunately, they were all flat-file, and I've never designed a relational database from beginning to end. So, I'm going to try this soon and see what happens.

[Dal Jeanis]

Sounds like a plan. But first, take a quick look at Access MVP Roger Carlson's site at http://www.rogersaccesslibrary.com/, and do his quick tutorials on database design. It will save you dozens of hours of pain and suffering, by investing a couple of hours now.

[warrenpage]

I don't think that site is geared for beginners or novices like me. Access for Dummies is more my speed, and even parts of THAT book are over my head! But thanks for the link.

[Dal Jeanis]

The tutorials on database design don't seem (to me) to take much prerequisite knowledge. The important thing is learning how to determine the "entities" that you are trying to represent in your database.

[orange]

warren,
As Dal suggested this tutorial will lead you through the basics -- identifying entities, attributes and relationships. It is key to understand the underlying concepts of relational database in order to use Access or other DBMS efficiently. The tutorial is geared to people new to database, but is also a good refresher.
http://www.rogersaccesslibrary.com/T...lationship.zip
Good luck.

[warrenpage]

I think I did it. I think I got it. I think I successfully created a form and subform for entering movie data and actor data that works. It might not be the best way to do this, though. But at least I've made an enormous amount of progress.

However, I do have one problem I'd like help with, if anyone's willing to assist.

First, let me show you my tables.

The asterisk (*) denotes the Primary Key.

tblMovie
*Movie ID
Title
Director
Timestamp

tblActors
*Actor ID
Actor Last Name
Actor First Name

tblJunction
Movie ID
Actor ID

I deliberately did not put any Primary keys in the Junction table, because when I did, the Relationship Wizard created one-to-one joins, which I knew couldn't be right. Without primary keys, each join is one-to-many. I enforced Referential Integrity, and I selected Cascade Update Related Fields.

Now…once the tables were set up, I used the Form Wizard to create a form and subform.

I selected all of the fields in tblMovie. I selected Actor First Name and Actor Last Name from tblActors, and I included Actor ID from tblJunction. The form and subform were created, and after entering some data, it appeared as though the relationships and data were stored correctly.

I'm quite happy. But, like I said, one problem, so would you please consider nudging me to the solution?

When I create a new record in the main form, and then go into the subform and enter the names of actors, I need to figure out how to get an actor's name to auto-complete if he/she is in there already. I'm wondering if I'm using the wrong Actor ID field?

For example: The first movie I entered is Jaws. The first actor I entered was Robert Shaw, who was assigned Actor ID #11. I also entered Richard Dreyfuss and Roy Scheider, whose ID's were 13 and 14.

With me so far? Now…the second movie I entered was The Sting. I entered the names Paul Newman and Robert Redford, whose Actor ID's were 20 and 21.

The problem: When I typed in the name Robert Shaw, it created a new Actor ID for him. I knew he was already entered in the database. I went into the Actors table and looked for him, and saw his ID was 11.

I erased his name and new ID, and in the ID box, typed in 11. When I did that, his name showed up.

Clearly, I need to tweak the subform. I would like the actor's ID and first name to be entered whenever I type in the last name. It will be much easier for me this way, rather than having to look up the Actor ID number for each actor. There will ultimately be hundreds of actors, making this a cumbersome and tedious task if I can't simplify it.

So…what should I do next, to enable auto entry of actors, based on last name? Can this be done? What should I do?

I hope this made sense and I hope I explained myself well. Thanks for all the help you've given me so far. Warren Page

[orange]

Watch this video on junction tables. https://www.youtube.com/watch?v=7XstSSyG8fw

[warrenpage]

That video really didn't change anything. The only difference between my database and the tutorial is that their junction table had two primary keys, and mine does not. I modified my junction table to have two primary keys, then I redid the form and subform, and the same thing is happening with the actors. I'm assuming I have the wrong Actor ID field in the subform, but I'm not sure.

However, I will say that I've seen numerous videos from Trainsignal. Heather's my lady! Those videos helped me more than just about anything else I've read or watched pertaining to Access. So, don't misunderstand me. I am grateful for you recommending the video. But I still need help with my subform.

[orange]

You will find with databases that getting the structures designed to support your "business requirement" is critical.
In the video, in the junction table, she took the fields representing the PK's of your other tables and placed them in the junction table.
Then she specifically made a composite primary key of those 2 fields in the junction table. Then she set up referential integrity among the tables by creating the appropriate relationships.

Get your tables and relationships established, then move to forms etc. You may wish to research the highlighted terms.

Good luck with your project.