query for finding a string
Hi,
When I need to qrite a query for which the value in the table is characters, I have to put them in quotes. In my situation because of a poor database design, there is field, called "country of education' and the has values like france - xxx university, USA-sraucse Uni. BAsically the field contains country and the name of university as well.
Now I need to search for people who have not studied from USA e.g. but I cannot say
........ WHERE Countrt_of_Education <> 'USA' because there are very few USA only. So I need to say <>'Any thing which contains USA'
Can anyone please guide me on how to do that?
Thanks and Regards
When I need to qrite a query for which the value in the table is characters, I have to put them in quotes. In my situation because of a poor database design, there is field, called "country of education' and the has values like france - xxx university, USA-sraucse Uni. BAsically the field contains country and the name of university as well.
Now I need to search for people who have not studied from USA e.g. but I cannot say
........ WHERE Countrt_of_Education <> 'USA' because there are very few USA only. So I need to say <>'Any thing which contains USA'
Can anyone please guide me on how to do that?
Thanks and Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually one more thing, I am not sure of, the values typed in are something like
France - University of Paris meaning there are even spaces in between, but i guess % should have counted those, no?
France - University of Paris meaning there are even spaces in between, but i guess % should have counted those, no?
ASKER
Got it thanks your LIKE predicate helped, got the NOT LIKE
In Access, the wildcard character is '*', not '%'.
Also, judging from your query, "people who have not studied from USA" is not precise enough. You need to choose between
"people who have never studied in the USA (but perhaps elsewhere)"
SELECT * FROM [Personal Data]
WHERE ID Not In (
Select ID From Education
Where Country_Of_Eduction Like 'USA*'
)
"people who have studied outside of the USA (but perhaps also in the USA)"
SELECT * FROM [Personal Data]
WHERE ID In (
Select ID From Education
Where Country_Of_Eduction Not Like 'USA*'
)
"people who have studied, but never in the USA"
SELECT * FROM [Personal Data]
WHERE ID In (
Select ID From Education
)
AND ID Not In (
Select ID From Education
Where Country_Of_Eduction Like 'USA*'
)
The last one assumes that a record in Education means "have studied".
Cheers!
(°v°)
Also, judging from your query, "people who have not studied from USA" is not precise enough. You need to choose between
"people who have never studied in the USA (but perhaps elsewhere)"
SELECT * FROM [Personal Data]
WHERE ID Not In (
Select ID From Education
Where Country_Of_Eduction Like 'USA*'
)
"people who have studied outside of the USA (but perhaps also in the USA)"
SELECT * FROM [Personal Data]
WHERE ID In (
Select ID From Education
Where Country_Of_Eduction Not Like 'USA*'
)
"people who have studied, but never in the USA"
SELECT * FROM [Personal Data]
WHERE ID In (
Select ID From Education
)
AND ID Not In (
Select ID From Education
Where Country_Of_Eduction Like 'USA*'
)
The last one assumes that a record in Education means "have studied".
Cheers!
(°v°)
Hmm. A bit slow... -- (^v°)
ASKER
SELECT [Personal Data].ID, [Personal Data].[First Name], [Personal Data].[Last Name], Education.Country_Of_Educt
FROM [Personal Data] INNER JOIN Education ON [Personal Data].ID = Education.ID
WHERE Education.Country_Of_Educt
Returns nothing... Actually I have to write a query for NOT Like, i mean WHERE Education.Country_Of_Educt