Combining multiple queries into one sql statement

[Guest]

I am having an off-day today and am not able to figure out how to get the
results I want from just one query. Instead I made multiple queries that
cascade from each other. But, I want to combine these queries together into
one query. Basically, I am trying to correct the ChangedBy field in the table
to fix typos. The field should only have values of {NULL, T.F., R.H., A.R.,
L.F., D.A., F.R.}. If the field is null then nobody has touched the record.
Otherwise, the field has someone's initials in it. But, people have put typos
in and I am trying to "fix" these typos. To fix it, I am checking to see if
the field is not null, then dump out the ones that are correct to leave me
with the not null and not correct initials. I then assign these typos to A.R.
So, the queries I have are:
qryFixChangedByStep1:
SELECT Compiled.* FROM Compiled WHERE (((Compiled.ChangedBy) Is Not Null));

qryFixChangedByStep2:
SELECT qryFixChangedByStep1.* FROM qryFixChangedByStep1 WHERE
((qryFixChangedByStep1.ChangedBy)<>"T.F."));

qryFixChangedByStep3:
SELECT qryFixChangedbyStep2.* FROM qryFixChangedbyStep2 WHERE
((qryFixChangedbyStep2.ChangedBy)<>"L.F."));

....You get the idea? I keep whittling down until I am ready to make the
update...

qryFixChangedBy:
UPDATE qryFixChangedByStep7 SET qryFixChangedByStep7.ChangedBy = "A.R.";


Is there a way to put this into one SQL statement?
TIA!

 

[Guest]

Hi Tim

To update the original table, use...

UPDATE Compiled
SET ChangedBy = "A.R."
WHERE ChangedBy IS NOT NULL
AND ChangedBy NOT IN ("T.F.", "R.H.", "L.F.", "D.A.", "F.R.", "A.R.")

Regards

Andy Hull

 

[John Spencer]

Why not the following to identify all the records that nee to be changed

This should identify the records that would be updated.
SELECT Compiled.*
FROM Compiled
WHERE ChangedBy Not In ("T.F".," R.H.", "A.R.", "L.F.", "D.A.", "F.R.")

IF that gives you the correct records then the Update query would be
UPDATE Compiled
SET ChangedBy = "A.R."
WHERE ChangedBy Not In ("T.F".," R.H.", "A.R.", "L.F.", "D.A.", "F.R.")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thank you very much. This works perfectly. I did not know that you can group
with the Not in (...) criteria. Wonderful!