Novice Question: Need it to only show if it meets both criteria...

[Lynne]

Hi,

I'm having a little bit of a problem.

I need this query to list all customers who own works by both Mark
Tobey and Salvadore Dali

This is what I tried:

SELECT CUSTOMER.CustomerID, CUSTOMER.Name, ARTIST.Name
FROM ARTIST, WORK, CUSTOMER, TRANSACTION
WHERE ARTIST.ArtistID=WORK.ArtistID And
CUSTOMER.CustomerID=TRANSACTION.CustomerID And
WORK.WorkID=TRANSACTION.WorkID And ARTIST.Name In ('Mark
Tobey','Salvadore Dali')
GROUP BY CUSTOMER.CustomerID, CUSTOMER.Name, ARTIST.Name
HAVING (Count(CUSTOMER.CustomerID))>1;


If I do that...it shows nobody, and if you take the having statement
off it shows people who have bought Mark or Salvadores stuff or both.

I just need both.


The results of that query (without the having statement added on) are:

CustomerID CUSTOMER.Name ARTIST.Name
----------------- ------------------------------ -------
1 Doe, Jane Mark Tobey
2 Rosen, Lynda Mark Tobey
2 Rosen, Lynda Salvadore Dali
3 Cooper, Tom Mark Tobey

All I want is Rosen, Lynda to show up..cause she has bought from both
the artists.

Thanks for any help!

 

[Michel Walsh]

Hi,


Remove the Artist.Name in the GROUP BY. If you group by artist, you would
get or Tobey, or Dali, in a given group, so the count will always be 1...
well, as long as nobody has two works by Tobey or two by Dali.


Hoping it may help,
Vanderghast, Access MVP

 

[Lynne]

Worked like a charm.

Thank you!

Hi,


Remove the Artist.Name in the GROUP BY. If you group by artist, you would
get or Tobey, or Dali, in a given group, so the count will always be 1...
well, as long as nobody has two works by Tobey or two by Dali.


Hoping it may help,
Vanderghast, Access MVP