Using AND in WHERE to find matches,evaluate,update another rec from evaluation result

[seattlemom41]

I figured out Access 2007 SQL (not on a server) for using multiple ORs in a query criteria to update a field, but using AND is giving me fits. In pseudo-code, here's what I want to do:



For each instance of (this) in field1, find specific instances of (thats) in field2. I have to specify what I'm looking for in the update query because I have to also do this for some (this)(thats) but not all (this)(thats).

If I find (this)(that) AND if the value in (another) is the same for the portion of (this)(that) that's a "subset", I want to change the value of (another) for one (that) to match the identical values in the subset.

Field1(this) Field2(that) Field3(another)
ABCD A1 No
ABCD A1a NA
ABCD A1b NA
EFGH A1 No
EFGH A1a No
EFGH A1b NA

DEFG (etc) (Perform same tests on DEFG, etc.)
(sorry I don't know how to indent these so the columns line up )

In the above dataset, A1a and A1b are the "subset" of A1. The value of field3 for ABCD for A1 needs to be changed to NA because A1a AND A1b both have NA in field3.

But EFGH.Field3 for A1 needs to be left alone since the values for A1a and A1b are not both NA.

Here's what I have, it runs, but it doesn't generate the right results. I'm stumped!

UPDATE tblOne.field1 INNER JOIN tblTwo.field1 ON tblOne.field1 = tblTwo.Field1
(I did the above to try to find only "ABCD" etc. in field1)
SET tblOne.field3 = "NA", 
(set some other stuff too)
WHERE (((tblOne.field2) Like "A1" AND (tblOne.field2) Like "A1a" AND (tblOne.field2) Like "A1b") AND ((tblOne.field3) Like "NA") AND ((tblOne.field2)="A1"));

I'm dazed and confused from trying to figure this out, LOL. I would really appreciate any help you can give!

[Stingaway]

Have you tried the ship in a bottle in a bottle approach? Filter down your first dataset with a query, then query off of that query for your next data set, lather, rinse, repeat. You would at least be able to see where your original query was going awry by looking at the dataset that each query provided you.

[orange]

Please see the following for proper SQL UPDATE query.

http://www.tek-tips.com/faqs.cfm?fid=1958

Note you update a Table and Set the value of a field.
Also an UPDATE query can change a lot of data if not constrained properly
(Where clause), so you may want to back up your tables before executing.