MS Access How can I create a query update for 3 fields if a match is detected?

I have 2 tables with the same field names. How can I produce a query update that copies 3 fields from table 1 to table 2 if a if a match is detected in 3 other fields?

Here are the fields that need to match in order for the other 3 fields to be copied:
Fields: (in both tables)
Atnum, Subnum & Patnum
Only 1 field needs to match.

If a match is detected, copy table one fields (all 3 fields) to table 2 fields:
ib, ibq & com
DJPr0Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Looks like you need 3 update statements. please try like below -
UPDATE table1
INNER JOIN (table2 ON table1.Atnum = table2.Atnum)
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window

UPDATE table1
INNER JOIN (table2 ON table1.Subnum = table2.Subnum)
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window

UPDATE table1
INNER JOIN (table2 ON table1.Patnum = table2.Patnum)
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try this -

Assuming columNametobeupdated  from Table1. We can use below query.

UPDATE T 
SET    T.columNametobeupdated = A.columnName 
FROM   table1 AS T 
       INNER JOIN 
				  (
				   SELECT A.* 
                   FROM   table1 AS A
                          INNER JOIN table2 AS B
                           ON A.Atnum = B.Atnum AND A.Subnum = B.Subnum AND A.Patnum = B.Patnum
                   ) AS A 
               ON A.Atnum = T.Atnum AND A.Subnum = T.Subnum AND A.Patnum = T.Patnum	   

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Edited my last comment. Please refresh the page and try.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mbizupCommented:
If only one match is required:

UPDATE Table1 AS t2, Table2 AS t2
SET t2.ib = t1.Atnum,  t2.ibq  = t1.Subnum, t2.com = t1.Patnum
WHERE t2.Atnum= t1.Atnum OR  t2.Subnum= t1.Subnum OR t2.Patnum = t1.Patnum

Open in new window


(My understanding is that you are trying to update ib, ibq and com in table 2 rather than overwriting Atnum, Subnum and Patnum.  Using OR in the criteria will make 'only one match' required.  If all three need to match use AND instead of OR)
0
 
DJPr0Author Commented:
Thanks Pawan,

3 Update statements - does this mean 3 separate queries update's that I need to run? I.E. I can not run all at the same time?
0
 
Pawan KumarDatabase ExpertCommented:
Yes run all the three update statements and u will get the results.
0
 
DJPr0Author Commented:
mbizup,

Your statement is producing dupes:
"your about to update 70,000 records"
Total records is about 12,000 and only approx. 8000 records should be updated.

I did make some changes to your update statement:

UPDATE Table1 AS t1, Table2 AS t2
SET t1.ib = t2.ib, t1.ibq = t2.ibq, t1.com = t2.com                  (copy 3 fields from table 1 to table 2 if a match is detected in the where statement) (don't mix the where fields with the fields to be copied)
WHERE t2.atnum= t1.atnum OR  t2.Subnum= t1.Subnum OR t2.Patnum = t1.Patnum;
0
 
mbizupCommented:
If you are trying to "copy 3 fields from table 1 to table 2", then you have the t1 and t2 backwards in the SET clause.  Try this:

UPDATE Table1 AS t1, Table2 AS t2 
 SET t2.ib = t1.ib, t2.ibq = t1.ibq, t2.com = t1.com                 
 WHERE t2.atnum= t1.atnum OR  t2.Subnum= t1.Subnum OR t2.Patnum = t1.Patnum; 

Open in new window

0
 
PatHartmanCommented:
One query will do it and you should use QBE to build it if you don't know the syntax.  

Add both tables to the QBE
Draw the three join lines
Select the fields you want to update from the table you want to update
Enter the names of the fields of the other table that you want the data to come from.  Using the format tablename.  You will get intellisense to choose the column name.

UPDATE Table1 AS t2 Inner Join Table2 AS t2 ON t2.Atnum= t1.Atnum AND t2.Subnum= t1.Subnum AND t2.Patnum = t1.Patnum
SET t1.fld1 = t2.fld1, t1.fld2 = t2.fld2, t1.fld3 = t2.fld3;
0
 
Pawan KumarDatabase ExpertCommented:
>>3 Update statements - does this mean 3 separate queries update's that I need to run? I.E. I can not run all at the same time?

Yes you need run three update statement. Yes you can run all the three at the same time.
0
 
PatHartmanCommented:
If the criteria is that all three fields are required for a match, then the solution is the one query I posted.

If You need to match on ANY ONE of the three, then the solution is Pawan's three queries.

Mbizp's one query is the equivalent of Pawan's solution and may work but I'm not positive since queries using Cartesian Products (cross joins) are not updateable.
0
 
DJPr0Author Commented:
mbizup,

Still producing dupes:
"your about to update 70,000 records"
Total records is about 12,000 and only approx. 8000 records should be updated.

Can we add "distinct" to eliminate the dupes?
0
 
Pawan KumarDatabase ExpertCommented:
@DJPr0 - Please try the suggestion I gave., that should work without any issues.
0
 
DJPr0Author Commented:
Pawan,

They do work but still have a dupe issue, the Patnum produces 59,000 updates.
I think the problem is some Patnum numbers are the same.
Is there a way to work this more efficiently by not updating if the same Patnum is detected?
If Patnum is found update the 3 fields, if the same Patnum is found again do nothing.
0
 
Pawan KumarDatabase ExpertCommented:
>>I think the problem is some Patnum numbers are the same.
Is there a way to work this more efficiently by not updating if the same Patnum is detected?

Yes.

Please try like this -

Added another condition  --> AND table1.Patnum <> table2.Patnum


UPDATE table1
INNER JOIN (table2 ON table1.Atnum = table2.Atnum AND table1.Patnum <> table2.Patnum )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window


UPDATE table1
INNER JOIN (table2 ON table1.Subnum = table2.Subnum AND table1.Patnum <> table2.Patnum )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window


 
UPDATE table1
INNER JOIN (table2 ON table1.Patnum = table2.Patnum AND table1.Patnum <> table2.Patnum )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window

0
 
DJPr0Author Commented:
Pawan,

I'm receiving you are about to update 0 rows, something isn't correct.
0
 
Pawan KumarDatabase ExpertCommented:
Are we getting the same message in all the three updates ?

Do you have NULL in patnum.?
0
 
DJPr0Author Commented:
Pawan,

Just tried with patnum,

Do you have NULL in patnum.?
Yes
0
 
Pawan KumarDatabase ExpertCommented:
Please try this - updated.

UPDATE table1
INNER JOIN (table2 ON table1.Atnum = table2.Atnum AND NZ(table1.Patnum,-99) <> NZ(table2.Patnum,-99) )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

 

UPDATE table1
INNER JOIN (table2 ON table1.Subnum = table2.Subnum AND NZ(table1.Patnum,-99) <> NZ(table2.Patnum,-99) )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com


 UPDATE table1
INNER JOIN (table2 ON table1.Patnum = table2.Patnum AND NZ(table1.Patnum,-99) <> NZ(table2.Patnum,-99) )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try this updated.


UPDATE table1
INNER JOIN (table2 ON table1.Atnum = table2.Atnum AND table1.Patnum = table2.Patnum OR table1.Patnum IS NULL or table2.Patnum IS NULL )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window

 

UPDATE table1
INNER JOIN (table2 ON table1.Subnum = table2.Subnum AND table1.Patnum = table2.Patnum OR table1.Patnum IS NULL or table2.Patnum IS NULL )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window

 
UPDATE table1
INNER JOIN (table2 ON table1.Patnum = table2.Patnum AND table1.Patnum = table2.Patnum OR table1.Patnum IS NULL or table2.Patnum IS NULL )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com

Open in new window

0
 
DJPr0Author Commented:
Pawan,

The third update statement produces dupes: (due to using Patnum for the join and logic)

I tried setting "Unique Records" to yes to your original statement, however this did not help.

UPDATE table1
INNER JOIN (table2 ON table1.Patnum = table2.Patnum AND table1.Patnum = table2.Patnum OR table1.Patnum IS NULL or table2.Patnum IS NULL )
SET table1.ib = table2.ib, table1.ibq = table2.ibq , table1.com = table2.com
0
 
mbizupCommented:
I think the dupes are coming from your "Only 1 field needs to match" criteria.   If PatNum matches, but the other two don't then you might have many records meeting the criteria... potentially with different values for ib, ibq and com.  This is ambiguous, and I think the updated fields will simply reflect whatever data the last record found contains.  

If you don't want duplicates in your query results, you need to tighten up your criteria so that only ONE record from table1 matches the criteria used for updating table2 (so you'll need something more specific than the "1 field needs to match" criteria.
0
 
Pawan KumarDatabase ExpertCommented:
The third update statement produces dupes: (due to using Patnum for the join and logic)
I tried setting "Unique Records" to yes to your original statement, however this did not help.

>> Which column is coming as duplicates ? Do we have any other column for comparison?
0
 
DJPr0Author Commented:
Pawan,

>> Which column is coming as duplicates ? Do we have any other column for comparison?
Patnum - an excess amount of records want to update. No all I have are three fields that need to be checked for matches.

Your original post works:


Just not sure why when I run the Patnum query update it updates 59,928 rows with a table of approx. 12,000 records. Must be updating the same rows multiple times for some reason, however I think the final product output is good.
0
 
mbizupCommented:
DJPr0,

<< Must be updating the same rows multiple times for some reason >>

If you take a moment to read my previous post, the first paragraph explains why this is happening, and also the potential problem with it.  It may or may not be an issue for you.
0
 
Pawan KumarDatabase ExpertCommented:
>>Must be updating the same rows multiple times for some reason, however I think the final product output is good.
Yes if both the duplicate rows matched then it must update both rows(or multiple) as they satisfied the criteria. Only rows which satisfies the criteria should update irrespective whether they have duplicates or not.
0
 
PatHartmanCommented:
@DJPr0 -- you never clarified your criteria.

How can I produce a query update that copies 3 fields from table 1 to table 2 if a if a match is detected in 3 other fields?
Sure sounds to me like you want to join on all THREE columns and yet you are in this confusing and lengthy discourse with Pawan because you don't understand why the counts don't make sense.  Well mbizip explained why the counts don't make sense.  Perhaps you should step back and reread the answers suggested by the other experts.

When tbbA has 4 instances of one value pf Patnum and tblB contain 6 instances of the same value, then the result set will be 24 records and each tblA record will be updated SIX TIMES!!!!! and the value you actually end up with will be random.  It depends on which tblB value Access ends up reading LAST.
1
 
DJPr0Author Commented:
Thanks to all the experts!
mbizup - your one query update did work, however it took an excessive amount of time to complete.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.