Update Query

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I need to update a field in one table as the product of two fields in
another table.

If I create a DSN called "test" and a linked server in SQL Server, the
following query will do what I need when I execute it from Query Analyzer:

UPDATE test...repamount
SET repamount = (c.splitamount * rs.splitpercent)
FROM test...repamount ra INNER JOIN test...checksplit c ON
c.checksplitid=ra.checksplitid
INNER JOIN test...repsplit rs ON rs.splitid=c.splitid
WHERE ra.checksplitid = 1

But when I try to translate this query into Access SQL, I get a syntax error
that really doesn't tell me what the problem is.

Here is my Access syntax:

sql_u = "UPDATE repamount " _
& " SET repamount = (c.SplitAmount * rs.SplitPercent) " _
& " FROM repamount AS ra INNER JOIN checksplit AS c ON
c.checksplitid=ra.checksplitid " _
& " INNER JOIN repsplit AS rs ON rs.splitid=c.splitid" _
& " WHERE ra.checksplitID = " & txtCheckSplitID.Value

Does anyone see where the error is?
 
Hi,

Try ( I didn't checked) :


UPDATE
(repamount AS ra INNER JOIN checksplit AS c ON
c.checksplitid=ra.checksplitid )
INNER JOIN repsplit AS rs ON rs.splitid=c.splitid

SET ra.repamount = (c.SplitAmount * rs.SplitPercent)

WHERE ra.checksplitID =parameter



( I am not sure if Jet accepts aliases for the updated table, in that case).

It is easier to start with a standard SELECT query, from the graphical
designer, and then, try, in data view, to see if the data can be, there,
updated, in the data view. If so, then, turn the SELECT query into an UPDATE
query, and keep the SQL view statement.




Hoping it may help,
Vanderghast, Access MVP
 
Ahh... so for a multi-table update in Access, the join is specified in the
UPDATE clause rather than in a FROM clause.

Thank you very much helping me out on this.

Dave
 
Back
Top