Solved Insert CustomerID in a related table which was not inserted initially (1 Viewer)

[nirmal]

Sir,
How to put the CustomerID which was not inserted in the related another table to the customer table.
e.g.
I have one tables Customer_tbl
And I created a Visit_tbl where I update the data using a update query using the Customer Name
Initially I had not inserted the CustomerID in the Visit_tbl
Now as of Normalization I want to to have the customer Id in Visit_tbl.
I can add the customer Id in the Visit_tbl one by one.
As there are more than 5000 rows in Visit_tbl, some time the same customer is repeated multiple times, it is time consuming.
Is there any way where by searching the FirstName, SecondName & LastName of the Customer, the CustomerID can be updated in the Visit_tbl in a single go.

 

[Minty]

Create a select query that joins the Visit table to Customer table but join it on those three fields.

See how many matches you get, and check for duplicates where you might Have two "John Does" as an example.

If that works then change it to an update query.
Take a back up before actually changing any data in case it goes wrong.

 

[nirmal]

I tried your way Sir, bbut with no output
Sending the zip file
Please help

 

[arnelgp]

there are names that does not "match" so you need to manually search it.

 

[Minty]

You have a problem with your data.
In your visit table you have most names with three parts.
In your customer table most names have four parts.

So if you join on three fields (1st, 2nd & 4th name) you get large number of matches, but not probably the correct ones.
Have a look and see what you get with this query:

SELECT Customer_tbl.FirstName, Customer_tbl.SecondName, Customer_tbl.ThirdName, Visit_tbl.CustomerName, Customer_tbl.CustomerID, Visit_tbl.VisitID, Visit_tbl.CustomerID
FROM Visit_tbl, Customer_tbl
WHERE ((([FirstName] & " " & [secondname] & " " & [Lastname])=[Visit_tbl].[CustomerName]));

 

[nirmal]

Yes Sir it is showing the data with some wrongly typed (by mistake whle doing Customer Id update manually) Customer Id in Visit table.
Sir now how can we Update the Customer Id in all matching rows in Visit table
Sir one more thing
There are total 5771 rows (visit number in Visit table) and the query output is showing only 5704 rows.
Sir why 67 rows are missing ?

 

[Minty]

Because the names do not exactly match.
Use the query and change it to an update query.
Set the CustomerID in the visit table to the Customer_tbl.CustomerID, where it is null in visit_tbl.

 

[Pat Hartman]

Did you do the update to add the custID yet?

use an inner join on the first update query. Join on the three fields. Update the custID on the visit table. That takes care of all the good data. Now you have to handle the rest of the data. So create a query that selects all the rows from the visit table where the custID is null. Then fix them, one at a time.