Very Simple Access Question for a ROOKIE !!

[saxonomy]

I have a table as such:

Name Acct# Value Change?
James 1211 0 A
James 1211 1 Y
Kelly 1311 B
Kelly 1311 1 Y
Nikki 1411 1 C
Nikki 1411 0 Y

Y represents the original value for the account numbers. A, B, or C represents the changes recommended, wherever necessary. How do I make a query to go through my table and replace the Y (original) values with the A/B/C (recommended) values in the same table, just a row beneath? If there is no recommended change (as in Kelly's case), I would want the query to ignore that account number.

Thanks in advance.

 

[Golom]

UPDATE myTable A INNER JOIN myTable B
       ON A.[Acct#] = B.[Acct#]

SET A.Value = B.Value

Where A.Change = 'Y' 
  AND B.Change IN ('A', 'B', 'C')
  AND B.Value IS NOT NULL

 

[saxonomy]

Thanks a lot.
One last modification.

Name Acct# Value Value2 Value3 Value4 Change?
James 1211 0 2 1 4 A
James 1211 1 0 2 1 Y
Kelly 1311 1 B
Kelly 1311 1 1 1 1 Y
Nikki 1411 1 3 3 3 C
Nikki 1411 0 0 0 0 Y

Basically, how can I do this for multiple columns of data?

Nice speedy reply, thanx.

 

[Golom]

With un-normalized tables as this is, it is much more difficult and prone to error. You could just run multiple separate queries like the one above, substituting "Value", Value2", "Value3", etc. in each separate query.

 

[saxonomy]

Good enough for me.
Thanks again !!!