Delete based on "if"

[Guest]

I have a query that has a property Id and a field for document type. Each
property ID can have up to 20 document types associated with it. However, I
want to delete the propery from my list if it contains a specific document
type(s) I am trying to filter. Then I would have left only the properties
that does not have the documents I filtered out.

Thanks, JLD

 

[Guest]

Hi

In the criteria row use

Not Like "something"

I have assumed the criteria is text - Hope this helps

 

[Guest]

Thanks for the response.

The problem is that if a the property has "property picture" in the document
type and then others also, I could have 10 different doccuments linked with
the property and I want to delete the property from the list entirely if it
has the one type 'property picture'. If I just filter out 'property picture'
I can still have the property in the list with the other types.

Thanks again.

 

[Guest]

Assuming that you set up referential tables for the propeties and the
document types the Not Like will exclude only the document types you dont
want and leave the others in the query.

Sorry but I am a bit lost with this.

If I was me I would use a form to provide the the criteria for the Not Like.
This could be a very simple combo based on the document type table. The
problem with using the stand alone Not Like is that it would be difficult for
a user (imposible for some) to stipulate the "not required" document type.

 

[Dirk Goldgar]

I have a query that has a property Id and a field for document type.
Each property ID can have up to 20 document types associated with it.
However, I want to delete the propery from my list if it contains a
specific document type(s) I am trying to filter. Then I would have
left only the properties that does not have the documents I filtered
out.

Possibly something along the lines of this SQL (adjusted for your table
names and the document type you want to get rid of, naturally):

DELETE FROM tblProperties
WHERE EXISTS
(SELECT PropertyID FROM tblPropertiesDocuments PD
WHERE PD.PropertyID = tblProperties.PropertyID
AND PD.DocumentType = "<bad document type>")

Please be sure to have a good backup of your database before you try
this, as it's completely untested.