Movie Database: Entering Both Multiple Directors and Multiple Actors?

[warrenpage]

Hello, I'm using Access 2013. I need to ask for help with my database design. I'm just a notch above novice, so please bear with me.



I've created a movie collection database, and it works to my satisfaction. I successfully created a junction table, and set up a many-to-many relationship for actors. I created a form and subform. The subform is for actors, and it works properly.

Once I finished the subform, I considered my database to be finished. Now, I am experimenting, for the purpose of learning more about database design. So, I decided to set up a relationship for directors. With the original database, I just had a plain text box where I enter the director's name.

With the second (experimental) database, I created a table just for directors. The table's fields are:

tblDirectors
DirectorID
DirectorLastName
DirectorFirstName
DirectorFullName (which is a calculated field [DirectorLastName] & ", " & [DirectorFirstName)

I know I could create a combo box, which I could use to choose the director's name. I've done that (after creating a query for Director's Full Name), and it worked.

But here's where I need help: I want to be able to select more than one director's name, in the event a movie has more than one director. For example, Monty Python and the Holy Grail has two directors, Terry Gilliam and Terry Jones.

I added a DirectorID field to my junction table.

I tried creating one main subform with MovieID, ActorID, DirectorID, DirectorFullName, and ActorFullName. I set the form to Continuous.

At this point, I need help. Not every movie will have more than one director. What is happening, whenever I enter data into the subform, is once I enter the DirectorID and the director's name appears in the first record, if I continue to add actors' names but no more directors, I get the following message:

"The Microsoft Access database engine cannot find a record in the table "tblDirectors" with key matching field(s) DirectorID."

So, do I create a separate subform just for directors? Can I keep the directors in the one subform? If I can, then what tweaking do I have to do to the database to enable this?

What is the most practical way of enabling the entry of both multiple actors, and multiple directors?

I'm attaching a copy in .zip format of my database, if anyone wants to take a look at it. (At least, I'm going to try to attach it.)

Thank you for any help you care to give. Hopefully it's just a minor tweak that I'm just overlooking due to my lack of experience. Warren Page

director_test_1.zip

[NTC]

to select more than 1 director you have a funamental choice to make: if the quantity can be reasonably certain never to exceed X and X isn't too many then simply add those fields to the table i.e. Director 1, Director 2, Director 3.

this is very common for say telephone numbers where people often have up to 3 but typically not more. the upside is it is simple. the downside is that to search for a number (or a director) you have to search all 3 fields....and there is of course the other downside in that it can't go above X ...

the other approach is just like you did with actors....make a separate table - and insert it as a subform.....no limit to the quantity and if you want to look up something from elsewhere in the database one is only looking up a single field of that table.

I would strongly recommend any suggestion to use a multi select box property.

[warrenpage]

Thanks for replying. That's why I posted this question in the Database Design area and not the Forms area. If you can estimate the maximum number of items in a record, then everyone would be designing flat-file databases.

I'm not familiar with multi select boxes. So, I Googled it, read a few sites, and it apparently uses a lot of VB code, unless I am not understanding it correctly. If I am, then I am not ready for VB. I do not want to use anything I don't understand.

Now...the separate subform: Is it possible to have more than one subform? If so, then what is causing the error warning I posted in my original post? I must not have the fields in my tables correct, or perhaps I don't have my relationships joined correctly?

Perhaps my junction table doesn't have the right fields? I'm not sure where to go from here.

[June7]

NTC, did you mean 'recommend against any suggestion to use a multi select box'?

A multi-select box works with a mult-value field without VBA code. It is multi-value field NTC is likely steering you away from.

Has any film ever credited more than 2 directors?

The movie/directors would have to be associated in a separate junction table and subform.

Don't repeat fullname in junction table.

[warrenpage]

Thank you for replying.

I need to ask a follow-up question or two, please.

First: You said the movie/directors would have to be in a separate junction table and subform. I already have a junction table and subform specifically for actors. So, my question is: Do I need to make a SECOND junction table, specifically for directors? If so, then I am assuming I need to put in MovieID and DirectorID and join that junction table with tblDirectors?

Are you saying I need to keep the directors separate from the actors? In other words, keep the DirectorID out of the first junction table? I cannot have one junction table for actors and directors?

I'll try it and see what happens.

I am not familiar with multi-select boxes and multi-value fields. Are they something I should look into, or is it preferable NOT to use them? What are the pros and cons of them?

Lastly: There are numerous films that have two--or even more--directors, fully credited. Singin' in the Rain (the classic musical) has two. Performance, from 1970, with Mick Jagger, has two. Four Rooms has four, including Quentin Tarantino.

New York Stories has three. And finally, 1967's Casino Royale had SIX! Although only five were officially credited. This doesn't even count any movies by brothers, such as the Coen Brothers.

WP

[warrenpage]

I think that worked. I just modified my database, added a second separate junction table, set up the relationships, added a second subform, and as of right now, it seems to be working. I have to run several tests to see if there are any bugs, but at least I'm not getting any error warning boxes like I did before.

I guess the trick is to make a separate junction table for each many-to-many relationship.

But out of curiosity, I'm still interested in the multi-select box you mentioned. I'll do a Google search and see if I can come up with anything.

[NTC]

gracious what a typo...yes definitely meant strongly recommend AGAINST using the multi select box feature.....

[June7]

Review http://office.microsoft.com/en-us/ac...010149297.aspx

I NEVER use them. http://social.msdn.microsoft.com/For...orum=accessdev

[warrenpage]

Thank you for the links. It's interesting, definitely, but I think what I have now is satisfactory. The purpose of designing this movie database was not JUST to have a movie database. The main purpose was to learn more about database design, features, and functions. I simply wanted to use a subject that I was familiar with, to make learning a bit easier for me. I've learned a lot, but obviously have a lot more to learn.

I'm going to experiment with THREE subforms, just to see if I can juggle them. I'll have subforms for actors, directors, and genre. (Genres include drama, comedy, western, sci-fi, mysteries, etc.).

When I'm done with that, I'm going to experiment with conditional formatting. I'm going to include a field for running time; the conditional formatting should enable me to search for movies that are under a certain running time, over a certain running time, or exactly a certain running time. That's my next project.

This thread was a huge help. Thank you to everyone who replied and assisted. Warren Page