Update query

[wizcow]

I am trying to repair a db.

The Invoice table has the Customer's name stored, but not the CustomerID.

I would like to repair the tblInvoice table data.
I'd like to have a query that looks up the customer name, then enters the proper customerID in the customerID field.
The customerID will have to come from the tblCustomers table.

I'm pretty lost with these update querys.

Tom

 

[Rich]

Use the Unmatched query wizard

 

[wizcow]

Rich

Thank you for the reply.

I implemented the wizard and then tried to chang the query to an update query, but I received an error saying it could not update the CustomerID field.

Any ideas?

Tom

 

[namliam]

this isnt an unmatched thing i dont think...

I think your looking for something like:

UPDATE tblCustomer 
INNER JOIN tblInvoice ON tblCustomer.CustName = tblInvoice.CustName 
SET tblInvoice.CustomerID = [tblCustomer].[customerID]
WHERE tblInvoice.CustomerID Is Null;

Hope this gives you a push in the right direction.

Regards