Solved Query to show difference in telephone and email stored in a table (1 Viewer)

[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

 

[Pat Hartman]

Create a query that joins the two tables on Cust_ID. Select the four fields from each table
Then in the criteria lines, compare each field to the corresponding field in the other table. Put each condition on a separate line to "OR" them. In SQL View, the Where Clause will look like:

Where Nz(tbl1.Phone_1, "") <> Nz(tbl2.Phone_1) OR Nz(tbl1.Phone_2, "") <> Nz(tbl2.Phone_2, "") OR Nz(tbl1.Email, "") <> Nz(tbl2.Email, "")

The point of the Nz() function is to convert null to a ZLS so the compare will work as you expect. Otherwise, any comparison where one of the two fields is null will be false. So (A <> Null) = False, not true as you would expect.

 

[Pat Hartman]

@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.

 

[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?

 

[Pat Hartman]

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

 

[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],"")));

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

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

There is also no reason to make a temp table.

 

[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?

 

[Pat Hartman]

You are over complicating this. Use the OR's but change the query to an update query. If ONE field changes, just update everything. If you want to update only the field that changed, then you will need x queries which is as many as there are fields that you will update. So, if you have four fields, use four update queries. One for each field you want to update if it is different.

 

[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

 

[Pat Hartman]

You can't. You can only control the update if you have a way to identify which contact changed.

It may be time to rethink the entire process. The data appears to be coming from some other application, possibly an ERP. Instead of trying to keep it in sync by updating your local copy, why not just link to the master tables? The second problem is that the feed you are using flattens a 1-m relationship and you don't seem to have the PK for the many side of the relationship. Can you ask the source people to update the feed to include it? That way, you know which m-side row to update for those data fields.

A different solution is to always create new tables from the feed and don't worry about updating them. You don't control the data, so you should not be updating ANY of the columns that come from the other application anyway.

 

[Pat Hartman]

Thank you for the like but just "liking" a post doesn't help anyone. I gave you THREE possible solutions. Are any of them suitable?