Update query or VBA request

[Guest]

I have a database with two tables (Table1 and Table2). Table1 has 21 fields
(Field1,Field2.....Field21). Table2 has 2 fields (Field1 and Field2)

Field1 to Field10 of Table1 has random numbers from 1-100. Field11 to
Field21 of Table1 has all BLANK records.

Field1 of Table2 has 100 records (These fields contains numbers from 1-100
in ascending order). Field2 of Table2 has 100 records (All random numbers
from 1 to 15)

I am looking for an update query or a VBA that will look for the number from
Field1 of Table1 in Field1 of Table2 and put the corresponding number (i.e.,
Field2 of Table2) in Field11 of Table1. (Similar to VLOOKUP in EXCEL).
Similarly, go to the next number and look for Field2 of Table1 in Field1 of
Table2 and put the corresponding number(i.e., Field2 of Table2) in Field12 of
Table1. Continue this till Field20 of Table1 and then move to the next record
of Table1.

Once everything is done, sum up the values from Field11 to Field20 in Table1
and put the value in Field21 of Table1. Do this till the last record.

Doing this thask in EXCEL is very simple and I can do it but my database has
1 Lac+ records which Excel cannot handle. Can anybody help me on this?

Maxi

 

[Guest]

Hello,

i'll try an answer
You need at least 2 steps / queries

UPDATE table1
Set field11 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field1),
Set field12 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field2),
.....
Set field20 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field10)

if this query becomes 'to big', try 10 single queries

At last do
UPDATE Table1 SET field21 = (field11+field12+... field20)

If you have to do this in only one step, you need some code.

Tino

 

[Guest]

Will this affect only record 1 of Table1 and I have to do the same thing
again and again for the rest of the records?

Also, do you want me to write the following in SQL window?
(Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field1),

I would go for the easiest way but writing code is not my cup of tea.