Update Query Problem

[mbrassel]

I'm new to Access and having a bit of difficulty. I have 2 tables. I want to
update one table with information from the other. More specifically, where
the records match between the 2 tables, I'd like to place info from 1 table
into the corresponding row of the other.
The matching fields between the 2 are: COMPARE_STATUTE. Where the
COMPARE_STATUTE is the same I'd like to update 1 table with info from the
other.
Here's what I've been trying in an update query:
IIf([KSA_Duties]![COMPARE_STATUTE]=[Reorder]![COMPARE_STATUTE],"[KSA_Duties]![New_ID]=[Reorder]![NEW_ID]"). Any help? Thank you in advance.

 

[ChrisJ]

try...

Update
KSA_Duties
SET
KSA_Duties.New_ID=Reorder.NEW_ID
from
KSA_Duties
INNER JOIN Reorder on
KSA_Duties.COMPARE_STATUTE=Reorder.COMPARE_STATUTE

 

[John Vinson]

I'm new to Access and having a bit of difficulty. I have 2 tables. I want to
update one table with information from the other. More specifically, where
the records match between the 2 tables, I'd like to place info from 1 table
into the corresponding row of the other.
The matching fields between the 2 are: COMPARE_STATUTE. Where the
COMPARE_STATUTE is the same I'd like to update 1 table with info from the
other.
Here's what I've been trying in an update query:
IIf([KSA_Duties]![COMPARE_STATUTE]=[Reorder]![COMPARE_STATUTE],"[KSA_Duties]![New_ID]=[Reorder]![NEW_ID]"). Any help? Thank you in advance.

Well... first off, it's VERY rarely either necessary or good design to
store data redundantly. If you can create a Query joining the two
tables on the New_ID field, you can use that Query to see data from
the two tables in conjunction.

Assuming that you're correcting missing or erroneous NEW_ID fields,
create a Query joining the two tables on COMPARE_STATUTE and update
using the query:

UPDATE [KSA_Duties] INNER JOIN [Reorder]
ON [Reorder].[COMPARE_STATUTE] = [KSA_Duties].[COMPARE_STATUTE]
SET [KSA_Duties].[New_ID] = [Reorder].[New_ID];

Copy and paste this into the SQL view of a new query. *BACK UP YOUR
DATABASE FIRST* and try running this query. You may need to create a
unique Index on COMPARE_STATUTE so that Access can uniquely identify
the record to be modified.

John W. Vinson[MVP]

 

[MGFoster]

I'm new to Access and having a bit of difficulty. I have 2 tables. I want to
update one table with information from the other. More specifically, where
the records match between the 2 tables, I'd like to place info from 1 table
into the corresponding row of the other.
The matching fields between the 2 are: COMPARE_STATUTE. Where the
COMPARE_STATUTE is the same I'd like to update 1 table with info from the
other.
Here's what I've been trying in an update query:
IIf([KSA_Duties]![COMPARE_STATUTE]=[Reorder]![COMPARE_STATUTE],"[KSA_Duties]![New_ID]=[Reorder]![NEW_ID]"). Any help? Thank you in advance.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Huh? Are you trying to run a query or is this in a form/report?

An update command (aka Action Query) would be like this:

Open a query in SQL View & enter this:

UPDATE KSA_Duties
SET New_ID = (SELECT New_ID FROM Reorder
WHERE Compare_statute = KSA_Duties.Compare_statute)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtRcOYechKqOuFEgEQKi8gCfRCkLYrvypXpo0WFHD2/AaJtjBvsAnjKL
/jv7voYMIm/TB7Dkd20W1mi8
=vcHU
-----END PGP SIGNATURE-----