Update query

[samotek via AccessMonster.com]

I want to write a function looking into a query :
SELECT Customers.afid, orders1.customerid
FROM Customers INNER JOIN orders1 ON Customers.Customerid = orders1.
customerid;

With the following condition :
If customers.afid = 3 , to update the order1.cusomerid to 124

Like that :
UPDATE Customers INNER JOIN orders1 ON Customers.Customerid = orders1.
customerid SET orders1.customerid = 124
WHERE (((Customers.afid)=3));

Also, when customers.afid = 4, to update order1.customerid to 320
I have 12 cases of the afid,so perhaps code is better
Etc etc

Can you help ?

 

[John Spencer]

It looks to me as if you just need an additional table with 12 records
TblChanges
Afid (Primary Key)
NewCustomerID

The following query may work for you - or it may not be updatable.
BEFORE you try this I would make a backup of my data and then check the
results.

UPDATE (Customers INNER JOIN TblChanges
ON Customers.Afid = tblChanges.Afid)
INNER JOIN Orders1
ON Customer.CustomerID = Orders1.CustomerID
SET Orders1.CustomerID = [tblChanges].[NewCustomerID]


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