Forum Discussion
Solved
Update Query - 3 criteria
Hello Experts,
I need to update 3 fields in a table Import_ExcelPC but I am not getting anywhere with it.
Let me explain what I need in simple plain english
Update:
Import_ExcelPC.Notes to the value show in [Import_ExcelPC_Copy].[Notes],
Import_ExcelPC.OKToPayYN to the value show in [Import_ExcelPC_Copy].[OKToPayYN],
Import_ExcelPC.DABYN to the value show in [Import_ExcelPC_Copy].[DABYN]
but its for records where
[Import_ExcelPC].[Invoice amount] = [Import_ExcelPC_Copy].[Invoice amount]
AND
[Import_ExcelPC].[Invoice No Stripped] = Import_ExcelPC_Copy.[Invoice No Stripped]
here is my sql:
UPDATE
Import_ExcelPC INNER JOIN Import_ExcelPC_Copy ON (Import_ExcelPC.[Invoice amount] = Import_ExcelPC_Copy.[Invoice amount]) AND (Import_ExcelPC.[Invoice No Stripped] = Import_ExcelPC_Copy.[Invoice No Stripped])
SET
Import_ExcelPC.Notes = [Import_ExcelPC_Copy].[Notes], Import_ExcelPC.OKToPayYN = [Import_ExcelPC_Copy].[OKToPayYN], Import_ExcelPC.DABYN = [Import_ExcelPC_Copy].[DABYN]
WHERE
(((Import_ExcelPC.Notes) Is Not Null)) OR (((Import_ExcelPC.OKToPayYN)<>0)) OR (((Import_ExcelPC.DABYN)<>0));
DABYN, OKToPayYN are Yes No fields.
NOTES is long text.
in picture:
the ouput is 0 records to update.
There should be 198 records being updated though.
I run this select query and it shows 198 records based on the criteria
SELECT Import_ExcelPC_Copy.Notes, Import_ExcelPC_Copy.OKToPayYN, Import_ExcelPC_Copy.DABYN
FROM Import_ExcelPC_Copy
WHERE (((Import_ExcelPC_Copy.Notes) Is Not Null)) OR (((Import_ExcelPC_Copy.OKToPayYN)<>0)) OR (((Import_ExcelPC_Copy.DABYN)<>0));
here is a screen shot. Its 198 records.
Why wont those 198 records update in Import_ExcelPC instead of showing 0 records to update?
I am not sure what I am doing wrong? thank you for the help.
Running the Select query on only the Copy table does not prove that the main table also has records with same values in the join fields.
Rather make a copy of the Update query, and switch to Select query.
Running the Select query on only the Copy table does not prove that the main table also has records with same values in the join fields.
Rather make a copy of the Update query, and switch to Select query.I have now figured it out. The update To criteria needs to be on the table you are grabbing the data FrOM but the update CRTERIA needs to be on the target table (as I initially thought but I was putting the update criteria on the other table. Little difficult to explain. All in all I had 3 colyumns but needed 6. I have never liked update queries when joining tables.
this works:
UPDATE Import_ExcelPC INNER JOIN Import_ExcelPC_Copy ON (Import_ExcelPC.[Invoice amount] = Import_ExcelPC_Copy.[Invoice amount]) AND (Import_ExcelPC.[Invoice No Stripped] = Import_ExcelPC_Copy.[Invoice No Stripped])
SET Import_ExcelPC.Notes = [Import_ExcelPC_Copy].[Notes], Import_ExcelPC.OKToPayYN = [Import_ExcelPC_Copy].[OKToPayYN], Import_ExcelPC.DABYN = [Import_ExcelPC_Copy].[DABYN]
WHERE (((Import_ExcelPC_Copy.Notes) Is Not Null)) OR (((Import_ExcelPC_Copy.OKToPayYN)<>0)) OR (((Import_ExcelPC_Copy.DABYN)<>0));
Hi Tom, thank you for the response.
<Running the Select query on only the Copy table does not prove that the main table also has records with same values in the join fields.
I am not sure what you mean but the join is on [Invoice No Stripped] and [Invoice Amount]. There are many records on this join criteria. I am positive of this.
I need to make sure I am understanding what is the main table.
Here are 2 queries
0 Hits
UPDATE
Import_ExcelPC INNER JOIN Import_ExcelPC_Copy ON (Import_ExcelPC.[Invoice amount] = Import_ExcelPC_Copy.[Invoice amount]) AND (Import_ExcelPC.[Invoice No Stripped] = Import_ExcelPC_Copy.[Invoice No Stripped])
SET
Import_ExcelPC.Notes = [Import_ExcelPC_Copy].[Notes], Import_ExcelPC.OKToPayYN = [Import_ExcelPC_Copy].[OKToPayYN], Import_ExcelPC.DABYN = [Import_ExcelPC_Copy].[DABYN]
WHERE(((Import_ExcelPC.Notes) Is Not Null)) OR (((Import_ExcelPC.OKToPayYN)<>0)) OR (((Import_ExcelPC.DABYN)<>0));
198 hits in below sql but its backwards meaning the table being updated is Import_ExcelPC_Copy but it should be Import_ExcelPC since that is the table I want to update.
UPDATE
Import_ExcelPC INNER JOIN Import_ExcelPC_Copy ON (Import_ExcelPC.[Invoice No Stripped] = Import_ExcelPC_Copy.[Invoice No Stripped]) AND (Import_ExcelPC.[Invoice amount] = Import_ExcelPC_Copy.[Invoice amount])
SET
Import_ExcelPC_Copy.Notes = [Import_ExcelPC_Copy].[Notes], Import_ExcelPC_Copy.OKToPayYN = [Import_ExcelPC_Copy].[OKToPayYN], Import_ExcelPC_Copy.DABYN = [Import_ExcelPC_Copy].[DABYN]
WHERE(((Import_ExcelPC_Copy.Notes) Is Not Null)) OR (((Import_ExcelPC_Copy.OKToPayYN)<>0)) OR (((Import_ExcelPC_Copy.DABYN)<>0));
Let me know your thoughts.
