SUBFORMS: Adding a Third Table Isn't Successful

[John W. Vinson]

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.

 

[T. Hulot]

John,

Thanks for your patience and encouragement with this.

I have to piece together all of the information in this thread, and
sort it out. I'll begin doing that on Tuesday afternoon.

As for the VBA code you mentioned: I have dabbled with it, on a very
low-level novice basis. It certainly won't hurt for me to try it.

Thanks again.

Jd

 

[Arvin Meyer [MVP]]

This is what I want, yes.

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

In Access the way to represent a many to many relationship is with a
junction table, sometimes, but rarely, called an intersect table. It
consists of a matching Primary Key to each of its related tables

The OrderDetails table in the Northwind sample database is just such a
table. Have a look at it in design view, and also view its relationships in
the Relationship Window.

 

[T. Hulot]

Hi everyone.

I've made one change to my database design, and would like to ask you
all if the revision is correct.

The change has to do with genre. I've taken Arvin's advice, and
decided to permit only one genre per movie. It took me a while to
change my mind, but that's where I'm at now.

I'll make a drop-down menu with genres, to keep the form less
cluttered.

Now that a Genre table is not a factor in the equation, I'm assuming I
am down to three tables--one being a junction table. As per John's
suggestions, here are my tables.

Can anyone please take a look at this design and tell me if I've
finally nailed it?

Movies:
MovieID - Primary Key
Title
Genre
Location
Running Time
Comments

(I may add such fields as Rating, Year of Release, and Studio in the
future, but not now.)

Crew:
CrewID - Primary Key
Last Name
First Name


Here's the junction table (which I've named CrewPosition), as I
understand it based on what John wrote:

CrewPosition
MovieID - Primary Key (Compound)
CrewID - Primary Key (Compound)
Position

IF this design is correct, then I only have two more questions, and
that's it for now.

1. Does this junction table require a compound primary key? If it
does, I assume it would be the two ID fields, is that correct? That's
how I designed it.

2. I want to make sure the joins are correct. Movies/MovieID to
CrewPosition/MovieID, and Crew/CrewID to CrewPosition/CrewID. Is that
correct?

Thanks for putting up with me and for being patient.

Jd

 

[John W. Vinson]

Hi everyone.

I've made one change to my database design, and would like to ask you
all if the revision is correct.

The change has to do with genre. I've taken Arvin's advice, and
decided to permit only one genre per movie. It took me a while to
change my mind, but that's where I'm at now.

I'll make a drop-down menu with genres, to keep the form less
cluttered.

Well... I'd use a dropdown (Combo Box to use its proper name) based on a small
Genre table. It's just easier to maintain and manage than a List Of Values
combo, and does not add very much to your database's complexity. It could be
just a one-field table with a Text field for the name of the genre as the
primary key.

Now that a Genre table is not a factor in the equation, I'm assuming I
am down to three tables--one being a junction table. As per John's
suggestions, here are my tables.

Can anyone please take a look at this design and tell me if I've
finally nailed it?

Movies:
MovieID - Primary Key
Title
Genre
Location
Running Time
Comments

(I may add such fields as Rating, Year of Release, and Studio in the
future, but not now.)

Sure. Looks good. Autonumber MovieID I take it? However, do avoid blanks in
fieldnames - I'd use RunningTime, LastName, etc.

Crew:
CrewID - Primary Key
Last Name
First Name

Again, you may want to add other biographical data as needed; that can be done
later.

Do put nonunique Indexes on any field you want to routinely search or sort,
e.g. Title, LastName.

Here's the junction table (which I've named CrewPosition), as I
understand it based on what John wrote:

CrewPosition
MovieID - Primary Key (Compound)
CrewID - Primary Key (Compound)
Position

Yup. You might want a little lookup table for position so you don't have to
type it every time and can easily add new positions.

IF this design is correct, then I only have two more questions, and
that's it for now.

1. Does this junction table require a compound primary key? If it
does, I assume it would be the two ID fields, is that correct? That's
how I designed it.

Yes and yes.

2. I want to make sure the joins are correct. Movies/MovieID to
CrewPosition/MovieID, and Crew/CrewID to CrewPosition/CrewID. Is that
correct?
Exactly.

Thanks for putting up with me and for being patient.

"The rain in Spain is mainly in the plain."
"She's got it! By George, I think she's got it!"

<g, d & r>

 

[T. Hulot]

John,
Thanks for your input and encouragement.
A follow-up question: I am not sure I know what a lookup table is.

Is there a tutorial you can recommend that explains or demonstrates
it, rather than my imposing further and asking you to teach it to me?

Or could you give a brief summary?

I'm assuming a little lookup table would mean adding a fourth table.
Is a lookup table joined to anything else?

Again, thank you.

Jd

 

[T. Hulot]

Another question, after re-reading this.

Including a separate Genre table as you describe below...I'm assuming
this would be linked to Movies/MovieID? Or something else?

And would this require a second subform, along with a subform for
Crew?

I'm not 100% sure I understand why I can't make a combo box as part of
the main form, and include Genre as a field in it.

What makes it easier to maintain? And what is the difference between
a regular combo box and a List Of Values combo box?

Jd

 

[John W. Vinson]

John,
Thanks for your input and encouragement.
A follow-up question: I am not sure I know what a lookup table is.

It's just a table. It's only a "lookup table" because that's how you would be
using it - it's a functional description.

Is there a tutorial you can recommend that explains or demonstrates
it, rather than my imposing further and asking you to teach it to me?

Or could you give a brief summary?

I'm assuming a little lookup table would mean adding a fourth table.
Is a lookup table joined to anything else?

You'ld simply create a new table named Genres, with one text field Genre,
making it the primary key. It would be a good idea (not absolutely necessary)
to define a one to many relationship from Genres to Movies, joining on the
Genre field - just so you can be sure that only those genres you have defined
get put into the field, and you don't end up with "Romantc Comodies" and the
like.

On your Form (*NOT* in the Movies table!!!) you would put a very simple combo
box using Genres as the rowsource, and Genre as the control source to let you
just pick the genre from the list. If you decide to add another genre you can
simply add it to the Genres table at any time (in the table datasheet, or you
can build a very simple maintenance form) and it will start showing up in the
combo box.

 

[John W. Vinson]

Another question, after re-reading this.

Including a separate Genre table as you describe below...I'm assuming
this would be linked to Movies/MovieID? Or something else?

Link by Genre. You cannot put a MovieID in the Genres table because each genre
applies to many movies!

And would this require a second subform, along with a subform for
Crew?

Nope. Don't make it harder than it is!

I'm not 100% sure I understand why I can't make a combo box as part of
the main form, and include Genre as a field in it.

That's precisely what I'm suggesting.

What makes it easier to maintain? And what is the difference between
a regular combo box and a List Of Values combo box?

To add or edit a genre in a List of Values combo box you must open the Form
itself in design view; view the Properties of the combo box; select the
Rowsource property; and edit a long string of semicolon delimited text
strings.

To add or edit a genre in a Table/Query based combo box - a "regular" combo if
you want to call it that - you open the table, enter a record, you're done.

 

[T. Hulot]

John (and anyone else following this thread),

I have a follow-up question or two about the lookup table you
mentioned.

However, due to the increasing size of this thread, and the fact that
we've veered from discussing forms to discussing tables, I'm going to
post any future table-related questions in the
microsoft.public.access.tablesdbdesign group, if that's all right.

I hope it is.

Jd

 

[John W. Vinson]

However, due to the increasing size of this thread, and the fact that
we've veered from discussing forms to discussing tables, I'm going to
post any future table-related questions in the
microsoft.public.access.tablesdbdesign group, if that's all right.

I hope it is.

Certainly, and see you there.

Note that it's generally not necessary to crosspost as in this thread - most
of us who answer cover several groups so we'll see it.