problem with Update query that has to fetch the values from another table

[san]

Hi,

I have the following update statement that updates one table by the
values from the other table which seems have some problem with the
syntax.

UPDATE tblResolve SET tblResolve.txtW hatIssue = tblIssues.Short Desc,
tblResolve.txtC ustomerDetails = tblIssues.Custo merDetails,
tblResolve.blnC ustomerLost = tblIssues.Custo merLost,
tblResolve.txtA ctualCost = tblIssues.Actua lCost, tblResolve.txtF UMLoss
= tblIssues.FUMLo ss, tblResolve.txtA ctionTakenToRes olve =
tblIssues.Actio nTakenToResolve , tblResolve.txtS uggestedResolut ion =
tblIssues.Sugge stedResolution
FROM tblResolve
INNER JOIN tblIssues ON tblResolve.inti ssuecode = tblIssues.Issue Code
WHERE tblResolve.inti ssuecode = forms!frmEnterI ssues!IssueCode
;

When I run this query i get the following error:
Syntax error (missing operator) in the query expression
'tblIssues.Sugg estedResolution
FROM tblResolve
INNER JOIN tblIssues ON tblResolve.inti ssuecode = tblIssues.Issue Code'

Can any one help me in getting this resolved.

Thanks in advance,
San.

[Jamey]

Your Inner Join needs to be moved up to follow the Update statement.
Something like:

UPDATE Table1 INNER JOIN Table2 ON Table1.lngPrima ryKey =
Table2.lngPrima ryKey SET Table1.strField = Table2.strField ,
Table1.lngField = Table2.lngField , Table1.blnField = Table2.blnField ,
Table1.dtmField = Table2.dtmField ;

Sounds like your storing a lot of the same or similar data in two
seperate tables, which is a big relational database no-no. You may just
want to maintain the Issues table till the issues are resolved then
post them to the Resolve table using an Append Query to avoid data
duplication in the interim. That SQL statement would be something like
this:

INSERT INTO Table1 ( lngPrimayKey, strField, lngField, blnField,
dtmField ) SELECT Table2.lngPrima ryKey, Table2.strField ,
Table2.lngField , Table2.blnField , Table2.dtmField FROM Table2;