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.txtWhatIssue = tblIssues.ShortDesc,
tblResolve.txtCustomerDetails = tblIssues.CustomerDetails,
tblResolve.blnCustomerLost = tblIssues.CustomerLost,
tblResolve.txtActualCost = tblIssues.ActualCost, tblResolve.txtFUMLoss
= tblIssues.FUMLoss, tblResolve.txtActionTakenToResolve =
tblIssues.ActionTakenToResolve, tblResolve.txtSuggestedResolution =
tblIssues.SuggestedResolution
FROM tblResolve
INNER JOIN tblIssues ON tblResolve.intissuecode = tblIssues.IssueCode
WHERE tblResolve.intissuecode = forms!frmEnterIssues!IssueCode
;

When I run this query i get the following error:
Syntax error (missing operator) in the query expression
'tblIssues.SuggestedResolution
FROM tblResolve
INNER JOIN tblIssues ON tblResolve.intissuecode = tblIssues.IssueCode'

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.lngPrimaryKey =
Table2.lngPrimaryKey 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.lngPrimaryKey, Table2.strField,
Table2.lngField, Table2.blnField, Table2.dtmField FROM Table2;