This is going to sound very simple but i have no idea how to do this, i have a table of actors names and the other colum films they have starred in,

below is a small example:

Name of Actors/Actresses Film Starred In
Aaron Eckheart Batman
Anne Hathaway Devil Wears Prada
Anthony Edwards Top Gun
Antonio Banderas Shrek Two
Antonio Banderas Shrek Three
Barry Tubb Top Gun


Im trying to do quiries to show what actors have been in shrek two and shrek three for example. Im guessing theres an AND function however have no idea how to implement this.

I can do a query to show what actors have been in shrek two or shrek three but am struggling to find a way of showing what actors have been in both shrek two and three.

Thanks for your help
James

In the criteria below films put;

"Shrek" AND "Shrek Two"

You can add as many AND statements as you like.

I tried that but i end up with a completly blank queiry report but there are 4 people who should come up. Or is it my access messed up somewhere

"shrek two" Or "shrek three"

Sorry I didn't think that through to the finish. You are not going to be able to do this in one step.

You will need a query that first selects all actors who have been in either Shrek or Shrek 2 as Mike375 says. You will then need a query that selects from your first query all the duplicate actor names. In this way you will select only those actors who have been in both films, as they will be the only actors to appear multiple times in your first query.

Have a look at my QRY_2 in the attached sample.

From your example it appears your DB may not be fully normalised. You may wish to read up on the subject.