Help - Subquery to update access Table

I have two tables and I would like to do a query to update one table using a subquery and not able to make it happen.

What I would like to do is to read using nITSR from table a and match the field (nITSR) with the xITSR from table b and then update the pPraceBillerAmount in table a from the Biller field in table b.

The query that I tried is;
SELECT tmpJSFExpense.nITSR, tmpJSFExpense.pRacticeBilerAmount, qryTOupdateTBLexpense.Biller
FROM tmpJSFExpense INNER JOIN qryTOupdateTBLexpense ON tmpJSFExpense.nITSR = qryTOupdateTBLexpense.xITSR;

I tried several options but continue to get error.
Suggestions will be appreciated. Or help with this query that will be great. Want to understand and learn the "how to" so that I can learn for the future.

Thank you for the help.

Luis

 

[PHV]

One way:

UPDATE tmpJSFExpense INNER JOIN qryTOupdateTBLexpense ON tmpJSFExpense.nITSR = qryTOupdateTBLexpense.xITSR
SET tmpJSFExpense.pRacticeBilerAmount = qryTOupdateTBLexpense.Biller

Depending on what is really qryTOupdateTBLexpense you may have an error saying that it's not updatable.

Another way:

UPDATE tmpJSFExpense
SET pRacticeBilerAmount = DLookUp("qryTOupdateTBLexpense", "Biller", "xITSR='" & [nITSR] & "'")

If xITSR is defined as numeric in qryTOupdateTBLexpense then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[101287]

Tried the first suggestions and access told me that it needs an update operation.

UPDATE tmpJSFExpense INNER JOIN qryTOupdateTBLexpense ON tmpJSFExpense.nITSR = qryTOupdateTBLexpense.xITSR
SET tmpJSFExpense.pRacticeBilerAmount = qryTOupdateTBLexpense.Biller

I will tried the second suggestion. PH thank you for helping me out the suggestions.

Second suggestion - Did not work because conversion erros. Don't understand and thank you again.

UPDATE tmpJSFExpense
SET pRacticeBilerAmount = DLookUp("qryTOupdateTBLexpense", "Biller", "xITSR='" & [nITSR] & "'")

 

[PHV]

You may try either this:

UPDATE tmpJSFExpense
SET pRacticeBilerAmount = Nz(DLookUp("qryTOupdateTBLexpense", "Biller", "xITSR='" & [nITSR] & "'"),[pRacticeBilerAmount])

Or this:

UPDATE tmpJSFExpense
SET pRacticeBilerAmount = DLookUp("qryTOupdateTBLexpense", "Biller", "xITSR='" & [nITSR] & "'")
WHERE nITSR In (SELECT xITSR FROM qryTOupdateTBLexpense)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[101287]

Thank you again but did not work. Maybe I need to change the data type in the query. The field to update is Numeric Double and maybe the query is text. I will check later and tried to figure out. Thank you for the guidance and hopefully I will figure it out. Later. TY, Luis

 

[101287]

PH - Any additional suggestions. The queries provided tried to do the updates but at the end MS Access give me the following error message:
Microsoft Access was unable to update all the records in the update query. Microsoft Access didn’t update 109 field(s) due to a type conversion failure, 0 record(s) due …etc.

Any ideas to overcome this problem. Please....

 

[101287]

PH - Change the first query to a table. Then check the format of the fields on both tables. You're first query Works ! Thank you a "bunch". Learn quite a lot with your suggestions.

Luis