Update Query with subquery with where clause to subquery?!

[bu]

My eyes are crossing and my head hurts, so hopefully someone can lend some
insight.

I am trying to create an update query that updates rows in a table with
values from the same table and can't seem to figure it out. Here is an
example:

ID ID# ITEM PRICE SPECIALCHARGE
1 1 AA 1.00
2 1 SP 1.50
3 1 BB 2.25
4 2 CC 11.00
5 2 SP 1.50
6 2 DD 12.25

MyTable has 5 columns: ID, ID #, Item, Price, SpecialCharge and the
SpecialCharge column is empty when the table is initially populated, but the
other fields contain values. One row for each set of ID # records will
have a Price value (Item="SP") that should be put into the SpecialCharge
column for every row that has the same ID # value. So after the update, the
table would look like this:

ID ID# ITEM PRICE SPECIALCHARGE
1 1 AA 1.00 1.50
2 1 SP 1.50 1.50
3 1 BB 2.25 1.50
4 2 CC 11.00 1.25
5 2 SP 1.25 1.25
6 2 DD 12.25 1.25

I am so totally confused on what I am supposed to put where that I am now
completely lost. Does anyone have any ideas on how I can do this with 1
single query?

thanks in advance,
B

 

[Van T. Dinh]

Try:

UPDATE [YourTable] AS Main INNER JOIN
[YourTable] AS Sub ON Main.[ID#] = Sub.[ID#]
SET Main.SPECIALCHARGE = Sub.PRICE
WHERE (Sub.ITEM = "SP")

Try not to use special characters (such as #) or spaces in names. These
only create problems when you write SQL / code.

 

[bu]

That worked like a champ!! And that is something I never would have come up
with!!

Thanks for your help!
B

Try:

UPDATE [YourTable] AS Main INNER JOIN
[YourTable] AS Sub ON Main.[ID#] = Sub.[ID#]
SET Main.SPECIALCHARGE = Sub.PRICE
WHERE (Sub.ITEM = "SP")

Try not to use special characters (such as #) or spaces in names. These
only create problems when you write SQL / code.

--
HTH
Van T. Dinh
MVP (Access)

My eyes are crossing and my head hurts, so hopefully someone can lend some
insight.

I am trying to create an update query that updates rows in a table with
values from the same table and can't seem to figure it out. Here is an
example:

ID ID# ITEM PRICE SPECIALCHARGE
1 1 AA 1.00
2 1 SP 1.50
3 1 BB 2.25
4 2 CC 11.00
5 2 SP 1.50
6 2 DD 12.25

MyTable has 5 columns: ID, ID #, Item, Price, SpecialCharge and the
SpecialCharge column is empty when the table is initially populated, but the
other fields contain values. One row for each set of ID # records will
have a Price value (Item="SP") that should be put into the SpecialCharge
column for every row that has the same ID # value. So after the update, the
table would look like this:

ID ID# ITEM PRICE SPECIALCHARGE
1 1 AA 1.00 1.50
2 1 SP 1.50 1.50
3 1 BB 2.25 1.50
4 2 CC 11.00 1.25
5 2 SP 1.25 1.25
6 2 DD 12.25 1.25

I am so totally confused on what I am supposed to put where that I am now
completely lost. Does anyone have any ideas on how I can do this with 1
single query?

thanks in advance,
B