Simple Update Query - Using records from an imported table (1 Viewer)

[Navyguy]

Good Day All

I have what seems to be a simple update query that I cannot get to work. Not sure if it is the newer version of Access or I am just doing something wrong (which is more likely the case LOL).

An imported table (new_info) contains random information that needs put into the matching records in Tbl_Contact_Info based on "Contract_Number".

Tbl_People and Tbl_Contact_Info have a 1 to 1 with Contract_Number being the PK.

Any assistance is appreciated.

Cheers

 

[Gasman]

Make it a Select query until you get the join correct, then make an Update query.

TBH, the test data is such, that is it hard to see if anything matches?

 

[arnelgp]

UPDATE Tbl_Contact_Info
LEFT JOIN new_info
ON Tbl_Contact_Info.Contract_Number = new_info.Contract_Number
SET
Tbl_Contact_Info.AD_Name = [new_info].[AD_Name],
Tbl_Contact_Info.Contract_Email = [new_info].[Contract_Email]
WHERE ((Not (new_info.contract_number) Is Null));

 

[Navyguy]

Thanks for your answers... still not working. I am going to try updating one field at a time and see what that brings... it is not making sense. I have double checked the field properties and all are the same in both tables. I have tried various joins with no positive results. I have other update queries with other tables that seem to work fine, but this one is not working.

There must be something in the structure I am not picking up on. While I am certainly not a pro, I do feel comfortable with access and have created numerous update queries previously...

I added another table (end_result) show the expected results of the update query, maybe this explains better the expected output to Tbl_Contact_Info.

Cheers
John

 

[arnelgp]

UPDATE Tbl_Contact_Info
    LEFT JOIN new_info
    ON Tbl_Contact_Info.Contract_Number = new_info.Contract_Number
SET Tbl_Contact_Info.AD_Name = IIf(IsNull(Tbl_Contact_Info.AD_Name),[new_info].[AD_Name],Tbl_Contact_Info.AD_Name),
Tbl_Contact_Info.Contract_Email = IIf(IsNull(Tbl_Contact_Info.Contract_Email),[new_info].[Contract_Email],Tbl_Contact_Info.Contract_Email)
WHERE ((Not (new_info.Contract_Number) Is Null));

Before:

After:

 

[Navyguy]

@rnelgp Thanks for this. I think you have led me to the issue I did not realize was present in the actual database.

In the table new_info some of the records do not have a matching record in the [Tbl_Contact_Info].[Contract_Number] so they are not updating. I just realized this when I looked at your graphic and noted that "Kiwi" was not added / updated which has a record in the [new_info].[Contract_Number] and not in [Tbl_Contact_Info].[Contract_Number]

Looking at the actual database, I realize that a vast majority of the "new Info" records do not have a matching record in the "Contact Info" table. Not even sure how this even happens, but I guess this is the result of trying to compile data from multiple sources...

Thanks for all your assistance. I think once I get the data entries sorted out, I should be good to go. I will need to create a few append queries to update the main table so I can update the contact information table.

Cheers

John

 

[Pat Hartman]

In the future. Try something other than

still not working.

That is no help to us at all.

Now that you know about left joins, you can at least identify which data is missing from the "right" side table.
Then do a Right join to find out what data is missing from the "left" side table while you're at it.

 

[The_Doc_Man]

Just as a side comment/question...

You note that you have a 1/1 relationship between two tables. Unless there is a security issue such that you are required to treat the two tables separately, a 1/1 relationship doesn't occur that often. Further, if you try to append records to one side, if relational integrity is turned on, you can't. (If you are NOT requiring relational integrity, it is easier.) But the theory says that something in a table that HAS a Primary Key should ONLY depend on that primary key - but you violate that rule. In specific, the data depending on that PK ALSO depends on which table it is in, which means it violates the pk dependency rule.

Admittedly, this is a fine point, but when you come across it, you would normally check as to whether you should have had two tables - or just one table with a few extra fields. it is still possible for various reasons, but the presence of the PK dependency rule makes it less likely.

 

[Pat Hartman]

Doesn't sound like there is actually a relationship between the two tables. Sounds more like they come from different sources and the OP is trying to merge them.

 

[Navyguy]

@Pat Hartman That is correct. There was an imported table (new_info) with information from another sources. Once the info was appended to the Tbl_People and then updated in the Tbl_Contact_Info , the table (new_info) was deleted.

Thanks again for the assistance.

Cheers
John

 

[Pat Hartman]

You're welcome. Glad you got the data imported.