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

  • Thread starter Thread starter Lynne
  • Start date Start date
L

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!
 
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
 
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
Click to expand...
 
Back
Top