Solved Query to show difference in telephone and email stored in a table

[Number11]

So i need to have a query to show any customers details that have changed in a master table v a local table

Cus_ID
Phone_1
Phone_2
Email

I have the same fields in both tables i need to have the query look at Master Table (Customer_Master) and Bring Back any records that do not match the Local Table (Orders) data, I will then use this query to make an update query to bring the Local Table upto date?

is this possible

 

[Number11]

@Number11 Thanks for the like but if my suggestion solved your problem, it is much more helpful to people who find this thread later if you specifically say that and then mark the thread as saved. Likes are all over the place. Many posters just mark every suggestion with a like as a method of saying thanks for even reading my question. So, you can't trust only the solution to be liked.

So it does work only when the customer is entered within the table 1 time, if they in the records with the same account number say different address, it throws them up as changed, like i need to filter to compaire by record id and not account?

 

[Number11]

We need to see your query. Open the QBE in SQL view and copy the string and paste it here.

INSERT INTO Update_Contact_Details ( Record_Id, Record_Type, Customer_Number, Surname, Phone1, Phone2, Email )
SELECT DISTINCT Order_Master.Record_Id, Order_Master.[Record_Type], Customer_Base.Customer_Number, Customer_Base.Surname, Nz([Customer_Base].[Phone_1],"") AS Phone1, Nz([Customer_Base].[Phone_2],"") AS Phone2, Nz([Customer_Base].,"") AS Email
FROM Customer_Base INNER JOIN Order_Master ON (Customer_Base.MPPW = Order_Master.MPPW) AND (Customer_Base.Postcode = Order_Master.Prop_Postcode) AND (Customer_Base.Surname = Order_Master.Surname) AND (Customer_Base.Customer_Number = Order_Master.Service_Order_Number)
WHERE (((Nz([Customer_Base].[Phone_1],""))<>Nz([Order_Master].[Phone1]))) OR (((Nz([Customer_Base].[Phone_2],""))<>Nz([Order_Master].[Phone2],""))) OR (((Nz([Customer_Base].,""))<>Nz([Order_Master].[Email],"")));

 

[Number11]

If you want all the conditions to be true, you need to use AND's rather than OR's.

I thought you were searching for ANY record which had at least one field that didn't match. You posted an append query. That does not update existing records. It adds new records. Please clarify. Are you using the where condition to prevent adding duplicates? That is different from what you originally requested.

So i create a new table of data and the use that table to update the master tables that way i can manually check the changes, but as i said its not working when customer is present twice same account number a different record id

 

[Number11]

Did You read what I said about AND and OR?????

There is also no reason to make a temp table.

So need to change OR to And? or can i use or along with any

as it may just be email gets update and the phone is ok?

 

[Number11]

@Pat Hartman
So this is working great and has been doing the job, now i have an issue so we have some accounts that have two sets of contacts like say

RECORD _ID | ACCOUNT_NO SURNAME
4110 | 9944510 |Blue
4111 | 9944510 |Smart

on first pass it changes both records to "Blue" then if i can again it changes both to "Smart" and so on in a ring

how can i over come this