LOOKUP Tables: How Do I Design and Join Them Properly?

[T. Hulot]

This message is for John, but, of course, I'd like anyone to respond.

John, I'm having trouble understanding the lookup tables concept that
you described in another group. I'm designing the movie database.

Right now, I have five tables, as per your suggestions to add Genre
and Position lookup tables.

Here are the tables as I have them now:

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

Crew:
CrewID - Primary Key
Last Name
First Name

Position:
MovieID - Compound Primary Key
CrewID - Compound Primary Key
Position

GenreLookup:
Genre (Text) - Primary Key

PositionLookup:
Position (Text) - Primary Key

I've entered various records into the GenreLookup and PositionLookup
tables.

The relationships are as follows:
Movies/MovieID - Position/Movie ID
Crew/CrewID - Position/CrewID
Movies/Genre - GenreLookup/Genre
Position/Position - PositionLookup/Position

Am I right so far? I realize we've already been through this in
another thread, but that was before I started dabbling with lookup
tables. So, I hope you're willing to bear with me here for a moment.

At this point, I'm not exactly sure where to go next. My dead end
begins with the LOOKUP tab in the Table Design window. At the bottom
of the window are two tabs, General and Lookup.

Do I do anything with the Lookup tabs? If I do, do I use the
GenreLookup and PositionLookup tables?

Do I use these tables, click the LOOKUP tab, and select Combo box
under Display Control? Or do I use other tables and enter LOOKUP
information in them, instead?

What about Row Source Type and Row Source? What do I select for them?

Or do I use the Lookup Wizard instead of all this?

How close am I to getting the tables finally done? <grin>

Thank you! Jd

 

[John W. Vinson]

This message is for John, but, of course, I'd like anyone to respond.

Answers inline...

John, I'm having trouble understanding the lookup tables concept that
you described in another group. I'm designing the movie database.

Right now, I have five tables, as per your suggestions to add Genre
and Position lookup tables.

Here are the tables as I have them now:

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

Genre and Location are just text fields I take it?

Crew:
CrewID - Primary Key
Last Name
First Name

Position:
MovieID - Compound Primary Key
CrewID - Compound Primary Key
Position

GenreLookup:
Genre (Text) - Primary Key

PositionLookup:
Position (Text) - Primary Key

looks good...

I've entered various records into the GenreLookup and PositionLookup
tables.

The relationships are as follows:
Movies/MovieID - Position/Movie ID
Crew/CrewID - Position/CrewID
Movies/Genre - GenreLookup/Genre
Position/Position - PositionLookup/Position

Am I right so far? I realize we've already been through this in
another thread, but that was before I started dabbling with lookup
tables. So, I hope you're willing to bear with me here for a moment.

Sure. Just be sure that Genre and Position have the same datatype (Text) and
the same size (25 characters or something else appropriate) in both the Movies
and lookup tables.

At this point, I'm not exactly sure where to go next. My dead end
begins with the LOOKUP tab in the Table Design window. At the bottom
of the window are two tabs, General and Lookup.

Don't use it AT ALL. Putting a Lookup field in a Table is NEVER necessary and
can be confusing. Just leave the Lookup tab set to Textbox.

Do I do anything with the Lookup tabs? If I do, do I use the
GenreLookup and PositionLookup tables?
Nope.

Do I use these tables, click the LOOKUP tab, and select Combo box
under Display Control? Or do I use other tables and enter LOOKUP
information in them, instead?

Neither. See below.

What about Row Source Type and Row Source? What do I select for them?

Or do I use the Lookup Wizard instead of all this?

How close am I to getting the tables finally done? <grin>

'
Your tables ARE done. That's *ALL* you need!

The way you'll use the lookup tables is on your Form in the next step; you can
use the Form design toolbox wizard to add a Combo Box to the form, bound to
the Genre field in the Movies table. The wizard will ask where the information
comes from - you'll select the GenresLookup table. You may want to actually
create a Query sorting the genres alphabetically and use that instead of using
the table directly.

 

[T. Hulot]

This is encouraging news.

If I encounter trouble with the form and any subforms, I'll post
questions in the FORMS newsgroup. It won't be until Friday afternoon.
Thanks again. Jd

 

[Evan Keel]

Why would he want have lookup tables with only one column and a text column
at that? Wouldn't he want something like:

GenreLookup(GenreID (PK), GenreDescription) with GenreID being a foreign key
in Movies.

Same idea with Positions.. That way, if I changed GenreDescription
from"Comedy" to "Classic Comedy" I would on have to make the change in one
place--the lookup table Genres. And it would be very easy to add new Genres.

Evan

 

[John W. Vinson]

Why would he want have lookup tables with only one column and a text column
at that? Wouldn't he want something like:

GenreLookup(GenreID (PK), GenreDescription) with GenreID being a foreign key
in Movies.

Same idea with Positions.. That way, if I changed GenreDescription
from"Comedy" to "Classic Comedy" I would on have to make the change in one
place--the lookup table Genres. And it would be very easy to add new Genres.

The same applies to the one-field table suggestion if you set Cascade Updates,
and it saves a join when you want to display the genre on a form or report.
Microsoft does its best to give the impression that a primary key must be an
Autonumber but that is not in fact the case!

 

[Evan Keel]

Hey, pretty good. Thanks for the info. I'm from old school DBMS.

Evan

 

[T. Hulot]

Okay, it's time to work on the form.

John:

I created a form, added a combo box for genres, and it's working fine.
To be more specific, each record retains the genre listing I selected.
If I did not want to add a crew list to the form, I could stop right
now and be happy.

My attempt at creating a subform has run into problems, of course.

Let me explain what I did, and maybe we can trace the problem.

I opened the main form, and selected Subform/Subreport from the
Toolbox.

The Subform Wizard came open.
I selected Use existing tables and queries.

I selected Table: Crew.
Fields:
Last Name
First Name

In the screen that says, "Would you like to define which fields link
your main form to this subform yourself, or choose from the list
below," I didn't know what to do, so I left everything blank. It's
set to Define My Own.

I named the subform CrewSubform.

I deleted the Form Footer.

The problem: The same list in the subform appears for all movie
records. If I update a record with new names, that's what appears in
every record.

So, I'm assuming either I designed the subform wrong, or the tables
aren't linked properly.

I haven't even bothered messing with the Position Lookup table, and
won't until I get this thing straightened out.

John, or anyone else following what I'm doing...how do I tweak the
subform and/or tables?

Thank you! Jd

 

[John W. Vinson]

Okay, it's time to work on the form.

John:

I created a form, added a combo box for genres, and it's working fine.
To be more specific, each record retains the genre listing I selected.
If I did not want to add a crew list to the form, I could stop right
now and be happy.

First step! Good.

My attempt at creating a subform has run into problems, of course.

Let me explain what I did, and maybe we can trace the problem.

I opened the main form, and selected Subform/Subreport from the
Toolbox.

The Subform Wizard came open.
I selected Use existing tables and queries.

I selected Table: Crew.

Well... that was your mistake. The Crew table is a list of people, who may be
crew on many movies. There is NOTHING about movies in the Crew table, so it
can't link to Movies on the movie form! The Subform should be based, not on
the Crew table, but on the Position table! You're using the subform to enter
the *positions* for this particular movie, not actors or directors into the
Crew table.

I'd create a separate Crew form, no subforms needed, just for entering people.
You can prepopulate the table with actors you know you'll be needing, or leave
that to later as you see fit (see below). Do enter at least two or three
people just to make testing easier though.

The Position subform should have just two combo boxes bound to CrewID and
Position respectively. The Position combo is easy - just base it on a Query of
the Position table sorting Position ascending, so you can easily pick a
position.

The Crew combo may be a bit more work. Create a Query based on the Crew table.
Select the CrewID in the first column, and put

CrewName: [LastName] & ", " & [FirstName]

in the second, so you can see "Chaplin, Charlie" or "Bogart, Humphrey".
Include, and sort by, LastName and FirstName in that order, but uncheck the
Show checkboxes under the FirstName and LastName fields since you can already
see them.

Let's stop here for the moment - the next step will get into Visual Basic for
Applications coding to allow new actors to be entered from the Not In List
event of the combo box.

 

[T. Hulot]

Why a crew combo?


Do you mean a separate combo box for last name, and first name?

Unless I'm misunderstanding you I really, really don't want that for
people's names.

Won't such a pair of combo boxes have a seemingly endless quantity of
names? Wouldn't that make such combo boxes unwieldly, to say the
least?

Or am I not grasping this, again?

 

[John W. Vinson]

Why a crew combo?


Do you mean a separate combo box for last name, and first name?

Unless I'm misunderstanding you I really, really don't want that for
people's names.

Won't such a pair of combo boxes have a seemingly endless quantity of
names? Wouldn't that make such combo boxes unwieldly, to say the
least?

Or am I not grasping this, again?

My idea is that you would simply tab into the combo box and start typing (say)
GAR. The combo's autocomplete feature would have it jump directly to "Garbo,
Greta"; you'ld hit Enter and she would instantly be added to that movie's
cast.

The combo is JUST A SINGLE COMBO, not a pair. It displays both the first and
last names (sorted alphabetically) and lets you pick a name. That's all it's
for!

 

[T. Hulot]

John,

I do like the idea of auto-complete, but I'm not sure if I want one
field for last name and first name. I'm thinking about it, and need
time to decide.

One question:

What happens if you have more than one person with the same last name?
Such as:

Groucho Marx
Harpo Marx
Chico Marx
Zeppo Marx

One other question, just out of curiosity:

Is it possible to create a set of radio buttons from a lookup table,
or is a combo box the only thing you can use a lookup table with?

Jd

 

[John W. Vinson]

John,

I do like the idea of auto-complete, but I'm not sure if I want one
field for last name and first name. I'm thinking about it, and need
time to decide.

The advantage of my suggested query is that you have *SEPARATE* fields - in
the table - for first and last name, but when you're selecting a name you see
*both* names concatenated.

One question:

What happens if you have more than one person with the same last name?
Such as:

Groucho Marx
Harpo Marx
Chico Marx
Zeppo Marx

With my suggestion you would see

Marx, Chico
Marx, Groucho
Marx, Harpo
Marx, Karl
Marx, Zeppo

and you can scroll down and pick the one you want.

You would have a problem if there are two people who have the same name, which
of course does happen.

One other question, just out of curiosity:

Is it possible to create a set of radio buttons from a lookup table,
or is a combo box the only thing you can use a lookup table with?

You can use a Listbox instead of a Combo box - doesn't drop down, it shows all
the rows. IMO that would not be practical for actors (there are too many!) but
might be just fine for genres. With a little more work you can use an Option
Group control with radio buttons - that works better with a two-field table
with a numeric GenreID; you'ld store 1, 3, 7 but the labels in the option
group control would be "Comedy", "Adventure", "Documentary".

 

[T. Hulot]

John,

A quick question before I resume working on this:

I don't think you mentioned how the two separate forms are linked
together. Is that where the VBA code you mentioned comes into play,
or is there something else that puts them together?

Jd

 

[John W. Vinson]

John,

A quick question before I resume working on this:

I don't think you mentioned how the two separate forms are linked
together. Is that where the VBA code you mentioned comes into play,
or is there something else that puts them together?

There will be *three* forms in play here: a main form for Movies; a separate
main form for Crew, to enter new actors, producers, etc.; and a Subform for
Position. You could actually have two different subforms if you want - one to
enter all the Crew into the position table on the Movies form, and (if you
wish) another one to enter all the Movies that a person has been in, on the
Crew form.

Assuming that you'll be mainly using the Movies form, the Position subform
would be in a Subform control on the Movies form. A Subform control is a box
containing a form; it has two important properties, a Master Link field and
Child Link Field. These control how the data is linked. In this case they
would both be set to [MovieID]. This setting will ensure that (a) only those
crew members who are in the Position table for this particular movie will be
displayed, and (b) any *new* records added to the subform will inherit the
current MovieID.

The Subform would have only two combo boxes, I'd expect - one bound to the
CrewID but displaying the person's name; the other bound to the Role field.

To add a new person to the Crew table you'll need to use the first combo box's
Not In List event. This will take a bit of VBA code but it's not hard.