Delete specific records

[Jen0dorf]

Hi



I wish to delete records that do not have a field with either "Westbury" or "Chapel Allerton".

ok I know it would be simpler to do it by hand - only 60 records in table but in the interests of increasing ability thought I'd use a query.

Simple huh?

I tried

SELECT tbl_ParishinDatabase.Parish, tbl_ParishinDatabase.ParishID
FROM tbl_ParishinDatabase
WHERE (((tbl_ParishinDatabase.Parish)<>"Westbury" Or (tbl_ParishinDatabase.Parish)<>"Chapel Allerton"));

And it did not work

cab someon tell me why?

thanks

Ian

[pbaldy]

Try And rather than Or.

[Micron]

I think OR is correct since the field likely does not contain both values in the same record. However, a Select query does not delete records.
Understand that the entire record will be deleted (when you write it correctly), not just the values in the fields you refer to.

[pbaldy]

I think OR is correct since the field likely does not contain both values in the same record.

Or is not correct when the test is <>. If a record is A and thus I want to exclude it, it meets the "OR <> B" test and thus will be included. Every record is either not A or not B.

[Micron]

I should have given the issue more thought. Was more focused on the Select query part.
Thanks for the correction.

[pbaldy]

No worries. It is counter-intuitive, but the <> flips the logic.

[Jen0dorf]

Hi

I have not as yet created the delete query but was following advice learnt long ago - ok a few months- to create a working query first before changing it to a delete, update or append.

So I was trying to take my list of 60 different parishes and delete all records except where the parish name is either westbury or chapel allerton.

Hence my code.

As to the learned comments I regret to say I have failed to follow.

I used Or and a previous attempt showed me that And would not work.

as to <> if in my query I type Not "Westbury"

Access changes it to <> "Wesbury"

and If I type in Not "Westbury" Or "Chapel Allerton"

Access changes it to <> "Westbury" Or "Chapel Allerton"

So could someone explain the following posr because I'm sorta lost

"Or is not correct when the test is <>. If a record is A and thus I want to exclude it, it meets the "OR <> B" test and thus will be included. Every record is either not A or not B."

THanks

Ian

[pbaldy]

Try

SELECT tbl_ParishinDatabase.Parish, tbl_ParishinDatabase.ParishID
FROM tbl_ParishinDatabase
WHERE (((tbl_ParishinDatabase.Parish)<>"Westbury" AND (tbl_ParishinDatabase.Parish)<>"Chapel Allerton"));

[Jen0dorf]

Hi

thanks for that, have to admit I don't understand why "And" works, the logic of it escapes me as my logic says that if I use And then both criteria must me met clearly this is the inverse logic refered to!

cheers

Ian

[pbaldy]

if I use And then both criteria must me met

You want both criteria met in this case, do you not?

You want the value to be not equal to A, AND you want it to be not equal to B.

[Jen0dorf]

Hi

point taken my logic was flawed.

After all that I had a mental blowout and thought of another and better way to do what I wanted!!

Added a field to the main table containing the parish names and made it a yes/no field. That way I can tick each parish as I add it to the database and uses the true attribute to limit a parish list to the ones I've added.

I must be learning something!!

cheers

Ian