How to create a multivalued lookup column(Step by step)?

[Kavinga]

Please help me to create a multivalued lookup column. I'm currently making a
movie database. This is for Movie genre. A one movie could have number of
genres.... and I want put them in a multivalued lookup column. I need step by
step...

Plzz help me.. its really urgent...

 

[Jeff Boyce]

Which version of Access?

Just for you or for multiple users?

?Level of experience w/ Access?

Please remember that these newsgroups are largely served by volunteers.
Someone may (or may not) have an answer to your post, and may (or may not)
be on-line to see it, and may/may not have the time to respond.

If your situation is "really urgent", consider hiring a consultant...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Evi]

If you are talking about putting a lookup column into your table I
absolutely refuse. They cause no end of confusion.

But assuming you are talking about a nice combo box in your FORM then
1. All your tables (as any Access table should) ought to have a unique
primary key field.
2.you need to have a link between your Movie Table and your Genre table.
So you'll have TblMovie
MovieId (primary key field) Autonumber
MovieTitle
other fields about the movie

TblGenre
GenreID PK Autonumber)
Genre

3. Now you need a link table

TblMovieGenre
MovGenID (PK)
MovieID
GenreID

In Table Design set up a Unique Index for MovieID and GenreID so that you
don't add the same movie to the same genre by mistake
Google "Duplicate Table Values" for this Newsgroup to see how to do this

Click on TblMovie when closed
Go to Insert
Form
Autform-Columnar
to create a single form

Open the form in design view
drag TblMovieGenre from the Database Window onto the Details section of the
form which will create a Subform. The wizard should kick in and let you link
this subform to the main form via MovieID

Save and close

Open the Subform itself in Design View.
Drag a combo box from the Tools toolbar onto the form.
The wizard should kick in. Base the combo on TblMovie, include MovieID and
MovieTitle in it. The Wizard should hide MovieID
Choose to 'Store the Value in' MovieID

Evi



..

 

[Jason Lepack]

"multivalued lookup column"
This is wrong from the get-go. A field should contain a single value.

The best way to handle this relationship is with table structure.

movies:
movie_id - PK
movie_title
release_date
other info about a movies
Multiple movies have the same title, you will need to decide how to
uniquely identify a movie, I've put in the movie_id as an autonumber

genres:
genre_name - PK

movie_genres:
genre_name - PK - relates to genres.genre_name
movie_id - PK - relates to movies.movie_id

The movie_genre table creates a "many-to-many" relationship between a
movie and it's genres, as well as a genre and it's movies.

Cheers,
Jason Lepack

 

[Tom Lake]

"multivalued lookup column"
This is wrong from the get-go. A field should contain a single value.

The best way to handle this relationship is with table structure.

movies:
movie_id - PK
movie_title
release_date
other info about a movies
Multiple movies have the same title, you will need to decide how to
uniquely identify a movie, I've put in the movie_id as an autonumber

genres:
genre_name - PK

movie_genres:
genre_name - PK - relates to genres.genre_name
movie_id - PK - relates to movies.movie_id


Aren't genre_name and movie_id considered to be Foreign Keys in the
movie_genres table?

Tom Lake

 

[Jason Lepack]

Aren't genre_name and movie_id considered to be Foreign Keys in the
movie_genres table?

Yes, they relate to the tables listed. They also combine to make the
primary key, since a movie and genre pairing should not be listed
twice.

Cheers,
Jason Lepack