RE: Update query or VBA request

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

> 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
.

Relevant Pages

  • if database exists... MS Query
    ... I'm trying to pull data from access to excel using MS Query. ... three Access databases that will ... Problem is that the data source is hard coded into the MS Query. ... else select table1.* from c:\app2\table1.mdb if exists ...
    (microsoft.public.excel.programming)
  • renaming tables for each customer, used in one report
    ... I have a report that relies on a query of a table of customer specific data ... day and so will have to import several different sets of excel files daily. ... I called the customer table "table1" and so all the code ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Displaying row no/Record Counting
    ... EnteredOn Date/Time when the record was added. ... You create a query that contains Table1. ... On each row of your query, you need to count the number of records in Table1 ... In> order to generate an alphabetical listing of these people,> I have to analyze the report in Excel and sort the> spreadsheet based on the client's name. ...
    (microsoft.public.access.queries)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... You have an unnormalized table (of course, as it came from a flat spreadsheet instead of a relational source), and is it has 100 columns. ... joined to the master, showing all the records, the query is not updateable. ... > Say Fred deletes record 79 from Table1, and then Betty changes the> phone ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)