Query Question

  • Thread starter ielmrani via AccessMonster.com
  • Start date
I

ielmrani via AccessMonster.com

Hi All,
I have a table like this:

ProvID Type Office
1 Rad-H Office1
1 Rad-A Office2
2 Rad-H Office1
2 Active Office2
3 Active Office1
3 Rad-H Office2
4 Active Office1
4 Active Office2

I would like a query to pull only provid that type does not contain the word
active

The query result will be:

ProvID Type Office
1 Rad-H Office1
1 Rad-A Office2


Thanks in advance

Ismail
 
A

Amy Blankenship

Open the query builder. Select your table.

Highlight all fields and drag them to the grid. Below "Type" in the
criteria row, type <> "Active"

HTH;

Amy
 
I

ielmrani via AccessMonster.com

This will give all except the word Active.
Amy said:
Open the query builder. Select your table.

Highlight all fields and drag them to the grid. Below "Type" in the
criteria row, type <> "Active"

HTH;

Amy
Hi All,
I have a table like this:
Click to expand...
[quoted text clipped - 22 lines]
Ismail
Click to expand...
Click to expand...
 
J

John Spencer

Basic strategy in this situation is to identify which record you don't want
in one query and then use that result to eliminate those records in a second
query. So you need to identify every ProvId that HAS an "Active" record and
then use that to eliminate them from your results.

One method
SELECT ProvID, Type, Office
FROM YourTable
WHERE ProvId Not IN
(SELECT ProvID
FROM YourTable
WHERE Type = "Active")

Another method
SELECT ProvID, Type, Office
FROM YourTable
WHERE NOT Exists
(SELECT * FROM YourTable as T
WHERE T.Type="Active"
and T.Provid = YourTable.ProvId)

IF you have a LOT of records then you can write this another way to be
faster, but a bit more complex to understand
SELECT Provid, Type, Office
FROM YourTable
WHERE ProvID In (
SELECT T2.ProvID
FROM YourTable as T2 LEFT JOIN
(SELECT ProvID
FROM YourTable
WHERE Type = "Active") as T1
ON T2.ProvID = T1.ProvID
WHERE T1.ProvID is Null)
 
Top