How to use update query

[rehanemis]

Hi experts,

I am facing problem regarding updating of certain field of table. For example i would like to take data from a column of Table1 and would like insert that data into particular column in Table2.

Suppose Table One has data:

Table 1
ID Name amount
1 a 0
34 b 0
3 ab 0

Table2
ID Name amount
34 a 400
1 b 900
3 ab 4500
There are about 2000 records in each table.

I would like to update column "Amount" of Table1 by taking data from Table2 "Amount" Field.

how can i update the amount column by keeping the ID field?

Looking forward of your answer.
Thanks

 

[MarkK]

All that data should be in one table with a type field, like, . . .

ID Name amount type
1  a    0      1
34 b    0      1
3  ab   0      1
34 a    400    2
1  b    900    2
3  ab   4500   2

. . . and that way you don't have move stuff from table to table, you can just edit it.

 

[rehanemis]

Thanks of your reply.

But I can't keep both tables in one because they have different fields. I have just showed the sample table. My Table1 contains about 30 fields and Table 2 contains 74 fields.

I only wants to take one column data into Table2 by keeping ID field.

 

[Vonik]

rehanemis did you mean to write this?

Table 1

ID    Name    Amount
1       a          0
34      b          0
3       ab         0

table 2

ID    Name    Amount
1       a          400
34      b          900
3       ab         4500

if so try this in SQL view (either way even if they have different names in both tables its only looking at the ID field)

UPDATE Table1, Table2 

SET [Table1]![amount] = [Table2]![amount]

WHERE ((([Table1]![ID])=[Table2]![ID]));

 

[plog]

My Table1 contains about 30 fields and Table 2 contains 74 fields

A table with 30 fields starts to make me think your structure is improper. 74 makes the alarms sound louder. Add to it your interest in an UPDATE query and I'm pretty sure you are doing things wrong.

Can you post a screen shot of the fields in your tables? Possibly the relationships screenshot? I really think you aren't using Access to its full potential.

 

[rehanemis]

Thanks Vonik.

Your mentioned query working perfectly...

Thanks again.