modifying data using another table

[ryetee]

I have a table that has a field that holds a total. Through historic reasons the total in a (relatively) small number of cases is wrong. I have analysed and worked out what the difference should be and have created a table with the value that is missing. I basically want to add the 2 together.

So I have

Table1 Field1 Value1

Table2 Field2 Value2

I want table 1 to be

Table1 Field1 (Value1+Value2)

Programming wise I would just have
Value1 = Value1 + Value2

How do I go about doing this in a query or some other whizzy way?

 

[JANR]

To update a table field with values from an other table you use an updatequery with an inner join on the field that links the two tables together.

ex:

UPDATE Table1 INNER JOIN Table2 ON Table1.SomeUniqueField = Table2.SomeUniqueField
SET Table1.Field1 = Table1.Field1 + Table2.Field2

That way the correct records are being updated, you do have common unique field in both your tables don't you?


JanR

 

[ryetee]

To update a table field with values from an other table you use an updatequery with an inner join on the field that links the two tables together.

ex:

UPDATE Table1 INNER JOIN Table2 ON Table1.SomeUniqueField = Table2.SomeUniqueField
SET Table1.Field1 = Table1.Field1 + Table2.Field2

That way the correct records are being updated, you do have common unique field in both your tables don't you?


JanR

Yes I have a common field. I couldn't get this to work in query design (probably something I was doing wrong) but got around it by saving what the total should be in new table3 and then updating table1 wth that value.