Update query or VBA request

[mac_see]

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

 

[tino]

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

 

[mac_see]

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.

 

[tino]

Hello,

this query will update all records in your table at one step - you have to
execute it only once.

Yes, you should enter the SQL Statement in the SQL Window - i think, it's
the best way for this nested query.
To do so :
Open Access, choose the tab <queries>, click <new>, coose <design> (sorry,
i'm german, i dont know if the entry is exactly named "design" in english
version, but it's the first entry in the menu) .
You get the query designer with the window for select tables. Close this
window.
Right - click in the designer - window an select <SQL-View>.

Then type the query in the SQL window.
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 field13 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field3),
Set field14 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field4),
Set field15 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field5),
Set field16 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field6),
Set field17 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field7),
Set field18 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field8),
Set field19 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field9),
Set field20 = (Select Table2.field2 FROM Table2 WHERE table2.field1 =
table1.field10)

Close the window an save the query.
Now you can execute the query with <Open>.

I hope you are succesfull - if there more prolems, please ask again.