Update Query Type conversion

[Bongard]

I am trying to run the most simple of update queries to update one
field in a table. Here is my SQL

UPDATE 22_LifeCoRoll INNER JOIN (CASH_CASH_SUMMARY INNER JOIN
[22_LifeCoRoll - Current] ON CASH_CASH_SUMMARY.CASH_CNTL_ID =
[22_LifeCoRoll - Current].CASH_CNTL_ID) ON
[22_LifeCoRoll].CASH_PTFL_SK = CASH_CASH_SUMMARY.CASH_PTFL_SK SET
[22_LifeCoRoll].New_Cash_Smry_Amt = "CASH_SMRY_AMT"
WHERE (((CASH_CASH_SUMMARY.CASH_SMRY_TYP_CDE)="PTFL"));


I have checked both fields [CASH_SMRY_AMT] and [New_Cash_Smry_Amt] and
they both have identical field properties. I don't know what I'm doing
wrong.

Please help - thank you,
Brian

 

[KARL DEWEY]

Try removing the quotes from SET [22_LifeCoRoll].New_Cash_Smry_Amt =
"CASH_SMRY_AMT" to this SET [22_LifeCoRoll].New_Cash_Smry_Amt
= [CASH_SMRY_AMT]

 

[John Spencer]

Perhaps what you need is

UPDATE 22_LifeCoRoll INNER JOIN
(CASH_CASH_SUMMARY INNER JOIN
[22_LifeCoRoll - Current]
ON CASH_CASH_SUMMARY.CASH_CNTL_ID =[22_LifeCoRoll - Current].CASH_CNTL_ID)
ON [22_LifeCoRoll].CASH_PTFL_SK = CASH_CASH_SUMMARY.CASH_PTFL_SK
SET [22_LifeCoRoll].New_Cash_Smry_Amt = [CASH_SMRY_AMT]
WHERE CASH_CASH_SUMMARY.CASH_SMRY_TYP_CDE="PTFL"

You may need to include the tablename as well as the field name in the Set
statement.
SET [22_LifeCoRoll].New_Cash_Smry_Amt = [Cash_Cash_Summary].[CASH_SMRY_AMT]

If Cash_Cash_Summary is an aggregate query (you are summing results from a
table) then you will get a message that you must use an updatable query.

By the way, it always helps if you tell us WHAT is not working? Are you
getting a syntax error, the wrong results, no results, a computer going up
in flames, etc.


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