UPDATE Query

[kirthi97]

Hi,

I have a table GUPI where I have p_key, AcctUnit, Employee, Description columns. Here p_key is the primary key for this table

I have to copy AcctUnit for each Employee where Description="Regular Earnings" to the AcctUnit of that Employee where Description = "401K". I used the following Query

[COLOR=red]
UPDATE gupi AS a SET [a].AcctUnit=(SELECT DISTINCT  [b].AcctUnit FROM  gupi AS b WHERE (([a].Employee=[b].Employee) AND ([b].Description="Regular Earnings")))WHERE (([a].Description="401K Match %") AND ([a].Account=4150));
[/color red]

When I am trying to run this query, this message is popping up

Operation must be an Updatable Query [/color red]

Can any one tell me whats wrong with this query and how to write it

Thanks

 

[PHV]

You may try something like this:
UPDATE gupi
SET AcctUnit=DLookUp("AcctUnit", "gupi", "Employee='" & [Employee] & "' AND Description='Regular Earnings'")
WHERE Description='401K Match %' AND Account=4150;

If Employee is defined as numeric, get rid of the single quotes around [Employee].

Another way you may try:
UPDATE gupi AS a INNER JOIN gupi AS b ON a.Employee=b.Employee
SET a.AcctUnit=b.AcctUnit
WHERE b.Description='Regular Earnings'
AND a.Description='401K Match %' AND a.Account=4150;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886