Multiple Field Search using a query.

[rangermndn]

Allow me to explain what I am trying to do first.

I have a table called actors, With fields of Actor ID (Primary Key) First
Name, Last Name.
I have a table called Videos, it contains fields for the Star and two
co-stars, using a actor Id Field to link the two tables together.

I am trying to create a select parameter query that will show the videos
that an actor was in by looking up the Last Name of the actor in all three
actor fields. It works fine when with only one of the actorId fields in the
query, but as soon as i add another Actor ID field to the query i either get
no information or information with improper information.

 

[Ken Snell \(MVP\)]

If you want to find all videos where the actor is in any of the three
fields, this union query will work:

SELECT Videos.*, Actors.*
FROM Videos INNER JOIN Actors
ON Videos.Star = Actors.[Actor ID]
WHERE Actors.[Last Name] = [Enter actor's last name:]
UNION
SELECT Videos.*, Actors.*
FROM Videos INNER JOIN Actors
ON Videos.CoStar1 = Actors.[Actor ID]
WHERE Actors.[Last Name] = [Enter actor's last name:]
UNION
SELECT Videos.*, Actors.*
FROM Videos INNER JOIN Actors
ON Videos.CoStars = Actors.[Actor ID]
WHERE Actors.[Last Name] = [Enter actor's last name:]

 

[John W. Vinson]

Allow me to explain what I am trying to do first.

I have a table called actors, With fields of Actor ID (Primary Key) First
Name, Last Name.
I have a table called Videos, it contains fields for the Star and two
co-stars, using a actor Id Field to link the two tables together.

I am trying to create a select parameter query that will show the videos
that an actor was in by looking up the Last Name of the actor in all three
actor fields. It works fine when with only one of the actorId fields in the
query, but as soon as i add another Actor ID field to the query i either get
no information or information with improper information.

Part of the problem is that your table structure is not properly normalized.
Storing data (a role, basically) in a fieldname is limiting!

With this structure, though, you'll need to create a query joining the Actors
table to the Videos table *three times* - once to each field. Use a Left Outer
join in all three cases (select the join line and choose "Show all records in
Videos and matching records in Actors); and use a criterion of

LIKE "*" & [Enter actor name:] & "*" OR IS NULL

on each.

 

[rangermndn]

If you want to find all videos where the actor is in any of the three
fields, this union query will work:

SELECT Videos.*, Actors.*
FROM Videos INNER JOIN Actors
ON Videos.Star = Actors.[Actor ID]
WHERE Actors.[Last Name] = [Enter actor's last name:]
UNION
SELECT Videos.*, Actors.*
FROM Videos INNER JOIN Actors
ON Videos.CoStar1 = Actors.[Actor ID]
WHERE Actors.[Last Name] = [Enter actor's last name:]
UNION
SELECT Videos.*, Actors.*
FROM Videos INNER JOIN Actors
ON Videos.CoStars = Actors.[Actor ID]
WHERE Actors.[Last Name] = [Enter actor's last name:]
--

Ken Snell
<MS ACCESS MVP>

Allow me to explain what I am trying to do first.

I have a table called actors, With fields of Actor ID (Primary Key) First
Name, Last Name.
I have a table called Videos, it contains fields for the Star and two
co-stars, using a actor Id Field to link the two tables together.

I am trying to create a select parameter query that will show the videos
that an actor was in by looking up the Last Name of the actor in all three
actor fields. It works fine when with only one of the actorId fields in
the
query, but as soon as i add another Actor ID field to the query i either
get
no information or information with improper information.

Thanks this works and it has pointed me in new directions on how to do things.