SUBFORMS: Adding a Third Table Isn't Successful

[T. Hulot]

Hello, I'm using Access 2003 and am a novice.

I'm creating a very simple (I hope) relational DVD collection
database, and am encountering a problem when I add a third table and
try to join it to the other tables.

Let me show you what my three tables consist of, before I explain the
problem.

Table 1 (Main):
MovieID (Primary Key)
Title
Director
Running Time
Location
Comments

Table 2 (Actors):
MovieID
ActorID (Primary Key)
Last Name
First Name

Table 3 (Genre):
MovieID
GenreID (Primary Key)
Genre

(Genre is movie type, such as Drama, Comedy, Foreign, etc.)

My goal is to have a repeating panel for the Actors, so I can type in
an unlimited number of Actors' names per record.

In addition, I want to enter an unlimited number of genres to a
record. (For example, Spaceballs is both a comedy and science
fiction. Star Wars is science fiction, adventure, and fantasy.)

I want to be able to delete a record. As I understand things, when
you create a relationship, you use the Enforce Integrity/Delete
Cascade option for this.

I successfully joined the first two tables via MovieID, and
successfully created a form with a subform for Actors. I've found no
problems yet with that form. I can add actors' names, and can delete
entire records via the Delete Cascade option.

The problem arises when I try to incorporate the GENRE table. I've
tried joining it to the MovieID field in Table 1, but I'm clearly not
joining the right things together, because when I use the Form Wizard,
it doesn't let me create a subform.

So, I'm not 100% sure I created Table 3 correctly. Did I select the
proper primary key?
Should I be doing the joining differently?


If you need further information from me, please let me know. I'm not
sure if I provided enough details to analyze the problem.

Also, please keep in mind that I am somewhat of a novice. Thank you
for your assistance.

 

[Arvin Meyer [MVP]]

You design is a bit off.

1. Genre is an attribute of the movie, not the other way around. So, remove
the MovieID from Genre, and add the GenreID to the main Movie table.

2. There is a many to many relationship between movies and actors. (1 movie
can have many actors and 1 actor can play in many movies). So, you need to
add another table, call it tblMoviesActors which has 2 fields, with a
compound primary key.

tblMoviesActors
MovieID - PK
ActorID - PK

The GenreID is displayed as a combo box in the main form, and the subform
has 2 controls, a hidden textbox for MovieID (the link field) and a combo
box for ActorID.

If you need a sample, look at the relationship of Orders and Products in the
Northwind sample database that came with Access.

 

[Rob Parker]

Hi Arvin (and Original Poster),

The OP wants a 1-many relationship between movie and genre, so suggestion 1
doesn't quite cut it here. He really needs something like:

tblGenres
GenreID - PK
Genre - text entry for each possible genres

and

tblMovieGenres
GenreID - FK for tblGenres
MovieID - FK for tblMovies

If he wants to prevent duplicates, he could set the combination of both
fields as the PK for this table.

Rob

 

[Evi]

The design should look like this if you only want to assign 1 genre to each
movie:
Table 1 (Main):
MovieID (Primary Key)
GenreID (in the relationships window, link this from GenreID in the
Genre Table)
Title
DirectorID (linked to DirectorID in the Directors table - *if you are only
going to assign 1 director per movie*)
Running Time
Location
MovieComments

Table2 - Genre
GenreID (autonumber, primary key)
Genre
GenreComments (don't use the same name, Comments, in this table as in the
other one - it will cause confusion eventually

Table3-Actors
ActorID (autonumber, primary key)
ActFName
ActSurName
other actor details
ActorComments

Table4 - Directors
DirectorID (Autonumber, Primary Key)
DirFName
DirSurname
other director details
DirectorComments

Table5 - MovieActor table
MovActID (autonumber, PK)
MovieID (linked from the Movies table)
ActID (linked from the Actor table
Role (the role that actor played in that movie)

Lets say you want to be able to assign several directors to a movie (some
films have more than one, don't they?)
Then you would take DirectorID out of the Movies table and have

Table6 MovieDirector
MovDirID (PK)
MovieID (linked from the movies table)
DirectorID (linked from the Directors table)
Any other details which apply only to that director in that movie


Once you have your table design right, then we can start working on that
form
Evi

 

[T. Hulot]

I don't think this is what I want, if I understand you correctly.
If I understand you correctly, then what you are doing limits the
number of genres and actors. Unless I'm totally misunderstanding you.

By adding a third table with a compound primary key (which I learned
about only this past week), and by creating a combo box, the user
cannot enter his own genres. The author of the database puts in a
predetermined set of genres and actors. The combo box would let the
user choose from a list.

Am I understanding you correctly or incorrectly?

I want the user to enter any genre he wants, and any actor he wants.

I've been told this causes "consistent data entry" problems, and there
is no possibility for "normalization." I've figured out what all of
this means, but I can live without consistent data entry. It might
not be sound from a database engingeering point of view, but it's what
I want to do.

By the way...about ten minutes after posting my message, I figured out
what I wanted to do. I haven't tested the search/query aspect yet, so
what I did may turn out wrong, but as of right now, after adding a
second subform via Table Control, it seems to be operating properly.

 

[Evi]

You are mistaken if you think that the number of genres or actors will be
limited using a combo box. You are thinking about the sort of combo that is
limited to stuff you type into it when you create it.
The sort of combo Arvin is referring to, is usually based on a table or
query. So you would have a form based on your Genres table and you can enter
as many Genres as you like, any time you like.
When you add a combo to your Movies table, using a wizard, it will give you
the option to base it on an existing table in your database. You would use
that option and base the combo on your Genres table.
If you want to add a new Genre you can open up your Genres form and enter a
new one to your Genres table where it will become available to your combo.
You can equally (*but only once your database design is correct*) add a
subform so that you don't have to switch to a new form. It depends how much
space you have on your screen - you can even drag your Genres form into your
existing form in Design View and link it by *GenreID* (details available if
this doesn't make sense).

But please don't waste your time creating forms and subforms until your
database design is right. Even if it looks right, you will see what we are
talking about very soon.
It does take some getting your head around to understand relational database
design and many of the books which you read can leave you feeling more
puzzed than before but your sort of project is the ideal way to learn. If
anyone here using 'jargon' which you don't understand, please ask and ask
again. Most of the people here don't want to puzzle or confuse anyone.
Evi

 

[Pete D.]

I would also move the director to a seperate table setup like actors. No
reason to have to add same director many times.

 

[John W. Vinson]

I would also move the director to a seperate table setup like actors. No
reason to have to add same director many times.

An alternative - perhaps a better one - would be to make the Actors table more
general: rather than just Actors, have a table which would list all the people
involved, with an additional field indicating the nature of their involvement
(actor, producer, director, assistant key grip, whatever).

 

[T. Hulot]

This is so true. I promise to read this thread carefully after Easter
weekend, and I'll take the time to try to absorb it. I feel like I'm
hacking away at this like a lumberjack with a tree stump. But at
least I am understanding things little by little.

 

[T. Hulot]

This is an interesting idea that I never thought of.

Sort of like a Cast and Crew table. I could even include
"screenwriter" and "music composer" or "cinematographer."

I like this idea...and it might make the database easier.

 

[John W. Vinson]

This is so true. I promise to read this thread carefully after Easter
weekend, and I'll take the time to try to absorb it. I feel like I'm
hacking away at this like a lumberjack with a tree stump. But at
least I am understanding things little by little.

There are lots of good resources to help get you up the steep and rocky
learning slope... try some of these, and the references therein:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Pete D.]

I concur, didn't exactly write that sentence correctly

 

[T. Hulot]

Hello.

Let me shift gears a little bit here.

After reading this thread, I've decided to change what I want.

I want a main table with the MovieID, the title, running time, and a
comments box.

I also want a subform with genres.

Now, after taking John's advice, I want a CREW subform, where I can
enter actors, directors, screenwriters, producers, etc.

Is this a more sensible approach than the one I originally had?

If so, then I need to figure out how many tables to write, and what
fields to add to them.

I'm curious about one thing: I think someone mentioned you can have
two primary keys in a table? Compound primary keys? I did not know
that was possible.

Here is how I created the tables, but I'm not sure about some of them.

Table1
MovieID - PK
Title
Running Time
Comments

Table2
GenreID - PK
Genre

Is my design for the Crew table (Table 3) corect?

Table3
CrewID - PK
Last Name
First Name
Crew Position

I'm not sure if I include MovieID in Table 3 or not.


Do I add a fourth table with compound primary keys, such as this, or
do I do something else?

Table4
MovieID
CrewID

Am I getting closer to understanding this?

(You can call me Jd if you'd like, by the way.)

 

[Arvin Meyer [MVP]]

No, I understood a movie to normally have a single genre. True a horror film
is also a drama, but I think horror really sufficiently defines it. If you
do want multiple genre's for a specific film, then by all means make a
junction table like I showed you for actors and movies.

I don't think this is what I want, if I understand you correctly.
If I understand you correctly, then what you are doing limits the
number of genres and actors. Unless I'm totally misunderstanding you.

By adding a third table with a compound primary key (which I learned
about only this past week), and by creating a combo box, the user
cannot enter his own genres. The author of the database puts in a
predetermined set of genres and actors. The combo box would let the
user choose from a list.

Am I understanding you correctly or incorrectly?

You can add a NotInList event that will open the genre main form to add a
new genre, or actor main form to add a new actor. You may not want to do
this because every misspelled genre or actor can now be added. By making
them go manually to the main form, it is easier to force a lookup before
allowing a new addition.

Similarly, you can add the crew. In fact, 1 table can be used for actor and
crew as a many to many so that instead of having separate tables, you can
build 1 table for all people. Then build a table for roles (actor, producer,
etc.) Now in the junction table, you can have a triple field compound
primary key.

MovieID, PersonID, RoleID

so that when you have someone like Clint Eastwood, they can be displayed 3
times for a given movie as an actor, producer, and director.

 

[Arvin Meyer [MVP]]

An alternative - perhaps a better one - would be to make the Actors table
more
general: rather than just Actors, have a table which would list all the
people
involved, with an additional field indicating the nature of their
involvement
(actor, producer, director, assistant key grip, whatever).

Actually, that is the only truly normalized way of doing it.

 

[John W. Vinson]

Hello.

Let me shift gears a little bit here.

After reading this thread, I've decided to change what I want.

I want a main table with the MovieID, the title, running time, and a
comments box.

I also want a subform with genres.

Now, after taking John's advice, I want a CREW subform, where I can
enter actors, directors, screenwriters, producers, etc.

Is this a more sensible approach than the one I originally had?

Certainly going to be more flexible.

If so, then I need to figure out how many tables to write, and what
fields to add to them.

See below...

I'm curious about one thing: I think someone mentioned you can have
two primary keys in a table? Compound primary keys? I did not know
that was possible.

You can only have one Primary Key in any table - BUT that Key can consist of
one field, of two fields, even of ten fields (though I've never had such a
table).

Here is how I created the tables, but I'm not sure about some of them.

Table1
MovieID - PK
Title
Running Time
Comments

Might be a few more fields if you want, such as studio, issue date, etc.

Table2
GenreID - PK
Genre

Is my design for the Crew table (Table 3) corect?

Table3
CrewID - PK
Last Name
First Name
Crew Position

Crew position should NOT be in this table. Woody Allen has been an actor, a
director, and a producer - he doesn't have "a" crew position, and in general
nobody else does either!

I'm not sure if I include MovieID in Table 3 or not.


Do I add a fourth table with compound primary keys, such as this, or
do I do something else?

Table4
MovieID
CrewID

MovieID, CrewID and CrewPosition - recording THIS person's position in THIS
movie. They might have a different position in a different movie, so the
position is properly an attribute of the MovieCrew table (do use meaningful
names for your tables rather than Table1, Table4 etc.!!!)

Am I getting closer to understanding this?

(You can call me Jd if you'd like, by the way.)

Sure thing Jd.

You'll also want a MovieGenre table with fields MovieID and GenreID if in fact
you want each movie to be classified under one *or more* genres.

As noted elsethread, your Movies form would have two subforms, based on
MovieCrew (your Table3) and MovieGenre (Table4). You can put combo boxes on
the subform to select the person or the genre, and include VBA code in the
combo box's Not In List event to allow the user to enter new people or new
genres.

 

[Pete D.]

I think your junction table is best idea, a search of sound of music
returned the following genre's
Biography / Drama / Family / Musical

 

[T. Hulot]

Crew position should NOT be in this table. Woody Allen has been an actor, a

director, and a producer - he doesn't have "a" crew position, and in general
nobody else does either!

John, I think you're nitpicking here just a little.

We can call it something other than CREW, if you'd like. The name
doesn't mean anything to me. The contents are what matters.

This table will let me list anyone involved with the making of the
film. And if one particular person has several positions, whatever
they may be, they can all be entered here.

Director
Producer
Actor
Actress
Supporting Actor
Supporting Actress
Screenwriter
Cinematographer
Composer


I am not really concerned about some of these positions, but this
table would LET me add them if I wanted to.

As noted elsethread, your Movies form would have two subforms, based on
MovieCrew (your Table3) and MovieGenre (Table4). You can put combo boxes on
the subform to select the person or the genre, and include VBA code in the
combo box's Not In List event to allow the user to enter new people or new
genres.

I'm leery of VBA, because I'm not a programmer, and wouldn't know how
to write it. And even if someone furnished me with the code, I'd be
uncomfortable with anything in the database that I don't understand.

Don't get me wrong--I would sincerely be grateful to anyone who'd take
the time to do that. But the purpose of this database is to learn
proper database design. Or, I should say, BETTER database design.

Quite frankly, at this point in time, consistent data entry is not a
critical factor in the equation. I understand the need, or desire,
for it, but for now, I'm focusing on getting the tables designed
properly. From there, I can learn which tables to join to which, and
the next step would be creating forms and subforms.

Reading tutorials and documentation is helpful, but interactive
communication is the best way for me to grasp these concepts.

Anyway, let me study your response and see what I can get out of it.

Thanks for the response on a holiday. Jd

 

[T. Hulot]

This is what I want, yes.

But I don't think I've heard the term "junction table."

Based on the comment below, it appears to be a table with all of the
primary keys from other tables listed in it, and they are all one
compound primary key within it.

I need to understand the purpose of it, and what to link it up to (or
up to it).

Yes, though...if Warren Beatty, for example, is the producer, writer,
actor, and director of a film, I want him to be listed in one table,
for each of these classifications, along with anyone else.

We're on the same page here. I just have to understand how to do it.

 

[John W. Vinson]

John, I think you're nitpicking here just a little.

We can call it something other than CREW, if you'd like. The name
doesn't mean anything to me. The contents are what matters.

I guess I miscommunicated. I have no concern at all for the name of the table
or for the name of the field - my concern is WHICH TABLE the position field is
in.

It should not be in the Crew table. It should instead be in the table relating
Movie to Crew, so that if you have *one* movie where Allen is an actor, you
can specify that he is an actor; if you have a different movie where he is the
director, you can specify THAT.

If you put the field in the Crew table then each person can have one and only
one role, no matter how many movies they are in.

This table will let me list anyone involved with the making of the
film. And if one particular person has several positions, whatever
they may be, they can all be entered here.

Director
Producer
Actor
Actress
Supporting Actor
Supporting Actress
Screenwriter
Cinematographer
Composer


I am not really concerned about some of these positions, but this
table would LET me add them if I wanted to.

Sure. Exactly. That's a given, and my suggestion will allow you to do so. I'd
even add another little Roles table with these values, just so you can use a
combo box based on it to save typing (and typos) on data entry.

I'm leery of VBA, because I'm not a programmer, and wouldn't know how
to write it. And even if someone furnished me with the code, I'd be
uncomfortable with anything in the database that I don't understand.

Well... then you'll face a bit of inconvenience. You would be able to *SELECT*
a person who is already in the Crew table into the subform, but you would not
be able to easily add a new person. You would instead need to open up a
separate form based on the Crew table, add a record, close that form, requery
the movies form (by pressing F9), and retrying the entry. The code is (or can
be) pretty simple and clear.

Don't get me wrong--I would sincerely be grateful to anyone who'd take
the time to do that. But the purpose of this database is to learn
proper database design. Or, I should say, BETTER database design.

Quite frankly, at this point in time, consistent data entry is not a
critical factor in the equation. I understand the need, or desire,
for it, but for now, I'm focusing on getting the tables designed
properly. From there, I can learn which tables to join to which, and
the next step would be creating forms and subforms.

Reading tutorials and documentation is helpful, but interactive
communication is the best way for me to grasp these concepts.

Anyway, let me study your response and see what I can get out of it.

Good luck!