SUBFORMS: Adding a Third Table Isn't Successful

I concur, didn't exactly write that sentence correctly
"John W. Vinson" wrote in message
...
On Sat, 22 Mar 2008 10:13:54 -0400, "Pete D."
wrote:

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

John W. Vinson [MVP]

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

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

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.

"T. Hulot" wrote in message
...
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, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"John W. Vinson" wrote in message
...

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

On Sun, 23 Mar 2008 13:24:14 -0400, T. Hulot wrote:

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

John W. Vinson [MVP]

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

"Arvin Meyer [MVP]" wrote in message
...
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.

"T. Hulot" wrote in message
...
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, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

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

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.

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, MCP, MVP

On Sun, 23 Mar 2008 17:53:58 -0400, T. Hulot wrote:

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.

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.



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.

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!
--

John W. Vinson [MVP]

On Sun, 23 Mar 2008 18:23:54 -0400, T. Hulot wrote:

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.

You're almost there!

My suggestion would involve *two* junction tables - MovieGenres and MovieCrew.
They should not be lumped into one monster table, they're separate issues.

The way you would handle Beatty is to have one entry for Warren Beatty (CrewID
317 maybe) in the Crew table; and four entries in the MovieCrew table, all
with the same MovieID, all with 317 as the CrewID, and with four different
Role values.
--

John W. Vinson [MVP]