Delete based on "if"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JLD - 26 Feb 2007 21:46 GMT

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

Reply to this Message

Wayne-I-M - 26 Feb 2007 21:53 GMT

Hi

In the criteria row use

Not Like "something"

I have assumed the criteria is text - Hope this helps

Signature

Wayne
Manchester, England.

> 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
[quoted text clipped - 3 lines]
>
> Thanks, JLD

Reply to this Message

JLD - 26 Feb 2007 22:20 GMT

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.

> Hi
>
[quoted text clipped - 11 lines]
> >
> > Thanks, JLD

Reply to this Message

Wayne-I-M - 26 Feb 2007 22:31 GMT

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.

Signature

Wayne
Manchester, England.

> Thanks for the response.
>
[quoted text clipped - 21 lines]
> > >
> > > Thanks, JLD

Reply to this Message

Dirk Goldgar - 27 Feb 2007 00:25 GMT

> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message