Query Question

[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

 

[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

 

[ielmrani via AccessMonster.com]

This will give all except the word Active.

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:

[quoted text clipped - 22 lines]

Ismail

 

[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)

 

[Amy Blankenship]

How is that different from what you want?

This will give all except the word Active.

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:

[quoted text clipped - 22 lines]

Ismail