Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple updates based on another field (Access 2K)

    Not exactly sure how to approach this. If I have a table that has two fields ID and Amount, and I have another table with the same two fields, is it possible to create a query that will update the first table with the Amount from the second. For instance, if the 2nd table has an ID of 1000 and amount of $4000, I want to be able to update the first table that has an ID of 1000 with the $4000 instead of the current value. Secondly, if this is possible, could the amount be added to the first table instead of replacing the amount?

    Thanks in advance for any assistance.

  2. #2
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple updates based on another field (Access 2K)

    Ok, solved the first part. It was simple, I just didn't think about it enough.

    Used

    UPDATE Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1 SET Table1.Field2 = <table2>.[field2]
    WHERE ((<table1>.[field1]=<table2>.[field1]));

    Now working on adding the second one to the first.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple updates based on another field (Access 2K)

    OK solved this problem too.

    I just created a make table query with 2 fields, field1 and expr1, which is the sum of field2 in table1 and table2. I then ran the update query listed in the post above, except I changed it to point to table3 and included expr1

    The make table is listed below

    SELECT Table1.Field1, <table1>.[field2]+<table2>.[field2] AS Expr1 INTO table3
    FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
    WHERE (((Table1.Field1)=<table2>.[field1]));

    and the update is listed here:

    UPDATE Table1 INNER JOIN Table3 ON Table1.Field1 = Table3.Field1 SET Table1.Field2 = <table3>.[expr1]
    WHERE (((Table1.Field1)=<table3>.[field1]));

    I then created a macro to run both in order.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •