Query?

[Guest]

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

 

[Guest]

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.

 

[Al Campagna]

KYMailman,
Use this criteria aganst the Actors field... Assuming frmMovies has a
search text control named FindActor...
and in that unbound field you entered "East" (with no quotes)

Actors
Like "*" & Forms!frmMovie!FindActor & "*"

Should find "EastMan", "Eastwood", "Easton" in your Actor string.

This is why it's not recommended that you concatenate names in one field.
A field should never contain more than one discreet piece of information.

Better that you create a table of Actors, and relate that table One to
Many with tblMovies, via some unique key field, such as MovieID. The Movie
(One) on the main form, and the Actors (Many) in a continuous subform.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[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]