Update query question

[Bdog05]

I have some transactional type data in a table. I am trying to update columns in that table with calculated values based on the data in the same table, but evidently I am unable to write a lookup value to the same table referenced in the subquery I am using to generate the value to be written back into the table.

I believe I need to set up a different table and write the calculated values into the new table, however I need to figure out how best to replicate the primary key from the first table to the new table. Can anyone point me in the right direction of how to best go about this?

 

[TonyGroves]

You can certainly "read and write" tables in one go.
For example:
[tt]
UPDATE tbl SET fld3 = fld1 + fld2
[/tt]
Is that what you mean?

 

[aHash]

I think what he means is that,

If a one row's value = 10
update with (2 * 10)
if the next row's value is 20
update with (4 * 20) and so on.

Bdog05,
I think you should write a procedure. May be you will need a cursor.

 

[Bdog05]

Sorry, I should have been more specific on what I am trying to do. Here's my table. AvgSaleslastweek is the value I am trying to write back into the table.

Date Salesperson Amount AvgSaleslastweek
1/1/05 Bob 50 50
1/2/05 Jon 100 100
1/2/05 Bob 75 62.5
1/3/05 Jon 50 75
1/4/05 Bob 250 125

So I've got a query like:

UPDATE t1 SET AvgSaleslastweek = (SELECT AVG(amount) from t1 INNER JOIN t1 as t1_1 on t1.salesperson = t1_1.salesperson where ((CurrentDate - t1.date) < 7)

I get an error in SQL saying I can't make this update to the t1 table. Being new to SQL, I tried the same thing in access and received a similar error that I couldn't write a value from a query based on a particular table back to a column in that table.