This is a continuation of question
http://answers.google.com/answers/threadview?id=444306 answered by
mathtalk-ga.  I closed the question by rating it while I still had
more to ask.

here is the new question:

Now that I have the select query complete, I need to do an update
process on the results of the Select query. 

I now need to update some of the records in the result set of the
Select Query we created.  The criteria are easy for me to describe but
I don't know how to translate it into SQL.

Here is a sample of the Select Query results:

HotelID  ChangedFromID  Verified  Address       Zip
9876                    No        123 Main St   45844
8765                    No        123 Main St   45844
1234                    Yes       123 Main St   45844

The criteria for records to be updated is that within any set of
"matching records" - (i.e. those with the same Address and Zip), take
the HotelID from the record with field Verified = "Yes" and use it to
update the HotelID of the other matching records.  AND for each
updated record, update the ChangedFromID field with the value of the
old HotelID before the update AND update the Verified field to "Yes"

I'd like an Update query to create the following result update result:

HotelID  ChangedFromID  Verified  Address       Zip
1234     9876           Yes       123 Main St   45844
1234     8765           Yes       123 Main St   45844
1234                    Yes       123 Main St   45844

In the case that there are multiple records within each matching set
with (Verified = Yes), there should be no update performed so that it
can be reviewed manually later.

Hi, jimvet-ga:

I'm not sure if we are still dealing with the same table as before,
which was called SupplierMaster (at least in the part which dealt with
creating an index).

First here's a query that does update only those records for which a
single "Verified" match exists, per your specification.  Below I
provide an alternative that does a bit more than this, but in what I
think is a conservative approach.

In SQL View:

UPDATE SupplierMaster AS C, SupplierMaster AS C1
SET    C.ChangedFromID = C.HotelID,
       C.HotelID       = C1.HotelID,
       C.Verified = True
WHERE C.Verified  = False
AND   C1.Address  = C.Address
AND   C1.Zip      = C.Zip
AND   C1.Verified = True
AND ( SELECT Count(*) FROM SupplierMaster as C2
      WHERE C2.Address     = C.Address
      AND      C2.Zip      = C.Zip
      AND      C2.Verified = TRUE
    ) = 1
;

What I'd suggest is that this unnecessarily restricts the updating of
records to cases where there is only one "Verified" matching row, when
what might be allowed is to update as long as there is only one
unambiguous HotelID to use (regardless of how many "Verified" rows may
currently have that same HotelID).

A more "liberal" query to do this last approach would be:

In SQL View:

UPDATE SupplierMaster AS C, SupplierMaster AS C1
SET    C.ChangedFromID = C.HotelID,
       C.HotelID       = C1.HotelID,
       C.Verified = True
WHERE C.Verified  = False
AND   C1.Address  = C.Address
AND   C1.Zip      = C.Zip
AND   C1.Verified = True
AND   NOT EXISTS ( SELECT * FROM SupplierMaster as C2
                   WHERE C2.Address     = C.Address
                   AND      C2.Zip      = C.Zip
                   AND      C2.Verified = TRUE
                   AND      C2.HotelID  <>  C1.HotelID
                 )
;

Note that instead of requiring that the number of verified matching
rows is 1, we use the "exists" clause (in a negative form) to make
sure that HotelID among any verified matching rows is unambiguous.

The UDPATE syntax for Access is one of the most "non-standard" aspects
of this product.  Let me know of any difficulties you encounter.  I
spent quite a bit of time checking the syntax as best I can without
"access" to your real tables, and making some educated guesses about
the underlying datatypes.

regards, mathtalk-ga

Very clear and concise.  Thank you!