Update Query

[luscioussarita]

Happy New Year Everyone,

I have two tables. One table needs to be update from data of another table.
Here is the problem I need to use two fields to give the correct answer.

TblA
Fld1 Fld2 Fld3
23 AB Name
23 CD Name

TblB
Fld1 Fld2 Fld3
23 AB ?
23 CD ?

Can you help me to create a query update that uses two fields to update one
field. I can do it with one but not two. The two fields create the unique
identifier. Any ideas?

Sarita

 

[John Spencer]

UPDATE tblB INNER JOIN TblA
ON tblB.Fld1 = tblA.Fld1 AND
tblB.Fld2 = tblA.Fld2
SET tblB.Fld3 = [tblA].[Fld3]

If you do not know how to use the SQL view to enter the query. In design
view
-- Add both tables
-- Drag from fld1 to fld1
-- Drag from fld2 to fld2
-- Select Query: Update Query from the menu
-- Select fld3 from tblB
-- type [tblA].[Fld3] into the UPDATE row.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Happy New Year Everyone,

I have two tables. One table needs to be update from data of another table.
Here is the problem I need to use two fields to give the correct answer.

TblA
Fld1 Fld2 Fld3
23 AB Name
23 CD Name

TblB
Fld1 Fld2 Fld3
23 AB ?
23 CD ?

Can you help me to create a query update that uses two fields to update one
field. I can do it with one but not two. The two fields create the unique
identifier. Any ideas?

Sarita

You will need a unique Index on the combination of Fld1 and Fld2 in order for
the query to be updateable. Does either table have a Primary Key? If not, open
the tables in design view; ctrl-click Fld1 and Fld2 so they're both selected;
and click the Key icon to make them a joint two-field primary key.

Then in your Update query, add both tables to the query grid; drag TblA.Fld1
to tblB.Fld1, and drag tblA.Fld2 to tblB.Fld2 - two join lines in the query.
Then update TblB.Fld3 to

=[TblA].[Fld3]

I do have to wonder why you're storing the same data in two different tables:
sounds like questionable design to me!

John W. Vinson [MVP]

 

[luscioussarita]

John,

Thank you very much! It worked like a charm. I wrote it the same way but I
didn't include = to the [tblA].[Fld3].

Sarita

UPDATE tblB INNER JOIN TblA
ON tblB.Fld1 = tblA.Fld1 AND
tblB.Fld2 = tblA.Fld2
SET tblB.Fld3 = [tblA].[Fld3]

If you do not know how to use the SQL view to enter the query. In design
view
-- Add both tables
-- Drag from fld1 to fld1
-- Drag from fld2 to fld2
-- Select Query: Update Query from the menu
-- Select fld3 from tblB
-- type [tblA].[Fld3] into the UPDATE row.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Happy New Year Everyone,

I have two tables. One table needs to be update from data of another
table.
Here is the problem I need to use two fields to give the correct answer.

TblA
Fld1 Fld2 Fld3
23 AB Name
23 CD Name

TblB
Fld1 Fld2 Fld3
23 AB ?
23 CD ?

Can you help me to create a query update that uses two fields to update
one
field. I can do it with one but not two. The two fields create the
unique
identifier. Any ideas?

Sarita

 

[luscioussarita]

Hi John,

This is a one off instance because the new data will include new fields
which the current table doesn't have. I am doing a test run on the new
queries.

Thank you for the advice!

Sarita

Happy New Year Everyone,

I have two tables. One table needs to be update from data of another table.
Here is the problem I need to use two fields to give the correct answer.

TblA
Fld1 Fld2 Fld3
23 AB Name
23 CD Name

TblB
Fld1 Fld2 Fld3
23 AB ?
23 CD ?

Can you help me to create a query update that uses two fields to update one
field. I can do it with one but not two. The two fields create the unique
identifier. Any ideas?

Sarita

You will need a unique Index on the combination of Fld1 and Fld2 in order for
the query to be updateable. Does either table have a Primary Key? If not, open
the tables in design view; ctrl-click Fld1 and Fld2 so they're both selected;
and click the Key icon to make them a joint two-field primary key.

Then in your Update query, add both tables to the query grid; drag TblA.Fld1
to tblB.Fld1, and drag tblA.Fld2 to tblB.Fld2 - two join lines in the query.
Then update TblB.Fld3 to

=[TblA].[Fld3]

I do have to wonder why you're storing the same data in two different tables:
sounds like questionable design to me!

John W. Vinson [MVP]