SUBFORMS: Adding a Third Table Isn't Successful

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.

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

"T. Hulot" wrote in message
...
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.

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


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

"T. Hulot" wrote in message
...
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.

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.



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.

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



"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?

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.



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.

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

"Evi" wrote in message
...
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



"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?

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.



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.

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]

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.



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.

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.



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

On Sat, 22 Mar 2008 16:20:29 -0400, T. Hulot wrote:

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

John W. Vinson [MVP]