Select statement 2

[Enea]

I have a table with clients and trips taken.

I need to find out the clients who went to Belgium and to Argentina.

I don't need to see those who went to Belgim only or Argentina only.

What is the select statement?

 

[pkailas]

SELECT Clients from

where trips = 'Belgium' AND trips = 'Argentina'

_______
I love small animals, especially with a good brown gravy....

 

[steven290]

select clients from tblname where trips='Belgium' and trips='Argentina'

 

[Enea]

In my test database it returns me one field, which is correct as only 1 person visited the two countries but the field is empty??

SELECT Clients.Name
FROM Clients
WHERE (((Clients.Trips)="Argentina" And (Clients.Trips)="Belgium"));

 

[pkailas]

Use a single quote ' instead of double quotes "

_______
I love small animals, especially with a good brown gravy....

 

[traingamer]

Do a self join (tested) like:

SELECT t1.clientID, t1.Trip, t2.Trip
FROM tblClienttrips t1 INNER JOIN tblClienttrips AS t2  ON t1.clientID = t2.clientID
WHERE (((t1.Trip)="Belgium") AND ((t2.Trip)="Argentina"));

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Enea]

thank you

 

[PHV]

Another way, without self-join:
SELECT clientID
FROM tblClienttrips
WHERE Trip In ('Belgium', 'Argentina')
GROUP BY clientID
HAVING Count(*) = 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Not to get too picky, PH, but wouldn't that include those clients who made two trips to Belgium or to Argentina and exclude those who made more than two trips to those countries?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

Good point Greg.