Query?

[KYMailman]

Hi folks. I need a simple answer and I'm a simple user! I set up a movie
database using the fields Movies, Length, Actors, etc. In the field Actors, I
listed all the actors in the movie with a comma between the names. How do I
search or query to find all the movies with one particular actor, say Clint
Eastwood in my database? Thanks

 

[Sninkle]

Best thing to do is redesign your database a little. The Actors info should
be in a separate table (call it Actors). Then create another table, this
will store the MovieID and the ActorID, create joins to this table. From here
you can create queries to find all movies with Clint Eastwood or whichever
else.

If you have to put commas in a field to store multiple results then it's
always best to create a new table and join the data.

 

[John W. Vinson]

Hi folks. I need a simple answer and I'm a simple user! I set up a movie
database using the fields Movies, Length, Actors, etc. In the field Actors, I
listed all the actors in the movie with a comma between the names. How do I
search or query to find all the movies with one particular actor, say Clint
Eastwood in my database? Thanks

STOP.

Your table design *IS WRONG*.

Fields in a table should be "atomic", storing only one piece of information!

You should consider a "many to many" relationship with table structures like:

Movies
MovieID
Title
ReleaseDate
<other info about the movie as a whole>

Actors
ActorID
LastName
FirstName
<other biographical data as desired>

Cast
MovieID <link to Movies>
ActorID <link to Actors>
Role <name of character, or "Director", "Producer", "3rd gaffer", etc.>

You could then use a Form based on Movies with a subform based on Cast, and
just use a combo box to *select* from the list of actors rather than retyping
the actor's name.

With your current structure you will need to use an (inefficient) search, such
as

LIKE "*Clint Eastwood*"

as a criterion for your Actors field (and hope that you didn't misspell it).


John W. Vinson [MVP]