Multiple Field Search using a query.

R

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.
 
K

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:]
 
J

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.
 
R

rangermndn

Ken Snell (MVP) said:
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>




rangermndn said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Query Help - Select from 2 tables 2
complicate query? 3
Grouping Names in Mail Merge 0
write a program 1
programing 2
how can i do this 1
trap look up error 1
Error 400 in VB code execution? 1

Top