Aceess 2003 Query using NOT operator?

[Trevor Aiston]

A database I have inherited has field 'Status'. Data is entered in to this
field from a range values in a combo box
"planning/design";"data collection";"data input";"data analysis";"report
preparation";"action plan preparation";"awaiting summary";"project
complete";"project postponed";"project cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding' "project
complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off mark.

Can anyone suggest a way forward.

Thank you
Trevor

 

[Rick Brandt]

A database I have inherited has field 'Status'. Data is entered in to
this field from a range values in a combo box
"planning/design";"data collection";"data input";"data
analysis";"report preparation";"action plan preparation";"awaiting
summary";"project complete";"project postponed";"project
cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding'
"project complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off
mark.

Can anyone suggest a way forward.

Thank you
Trevor

WHERE Status Not In("project complete", "project postponed", "project
cancelled")

 

[Bob Barrows [MVP]]

A database I have inherited has field 'Status'. Data is entered in to
this field from a range values in a combo box
"planning/design";"data collection";"data input";"data
analysis";"report preparation";"action plan preparation";"awaiting
summary";"project complete";"project postponed";"project
cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding'
"project complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off
mark.

Can anyone suggest a way forward.

The easiest way is:

WHERE NOT [status] IN ("project complete","project postponed","project
cancelled")

This can be rewritten as:

WHERE [status] <> "project complete" AND [status] <> "project postponed" AND
[status] <> "project cancelled"

I prefer the easy way

 

[Bob Barrows [MVP]]

A database I have inherited has field 'Status'. Data is entered in to
this field from a range values in a combo box
"planning/design";"data collection";"data input";"data
analysis";"report preparation";"action plan preparation";"awaiting
summary";"project complete";"project postponed";"project
cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding'
"project complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off
mark.

Can anyone suggest a way forward.

PS. A more robust, data-driven approach, especially useful when you have to
write multiple queries that need to exclude the same set of status values,
is to create a table, perhaps called "StatusValuesToExclude", enter the list
of status values you wish to exclude, and do a left join to it, filtering
out the records where the staus from the "exclude" table is not null. Like
this:

select ...
from tablename as t left join StatusValuesToExclude as e
on t.[status]=e.[status]
where e.[status] is not null

 

[Bob Barrows [MVP]]

A database I have inherited has field 'Status'. Data is entered in to
this field from a range values in a combo box
"planning/design";"data collection";"data input";"data
analysis";"report preparation";"action plan preparation";"awaiting
summary";"project complete";"project postponed";"project
cancelled";"unknown";"long term"

I want to create a query that will return all records 'excluding'
"project complete";"project postponed";"project cancelled" values.

I've played with the NOT operator but think I'm probabably way off
mark.

Can anyone suggest a way forward.

PS. A more robust, data-driven approach, especially useful when you have
to write multiple queries that need to exclude the same set of status
values, is to create a table, perhaps called "StatusValuesToExclude",
enter the list of status values you wish to exclude, and do a left join to
it, filtering out the records where the staus from the "exclude" table is
not null. Like this:

select ...
from tablename as t left join StatusValuesToExclude as e
on t.[status]=e.[status]
where e.[status] is not null

Sigh ... I was in too much of a rush. This of course, is backwards. To
exclude the statuses in StatusValuesToExclude you want to only include the
results where e.[status] contains null:

.... where e.[status] is null