Results 1 to 3 of 3
  1. #1
    Confused is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    2

    Post Update 2 fields based on where condition.

    I'm trying to update a table of phone numbers from another table
    updating the phone # in table 1 if the field is not populated already.
    I can get the code to work individually but not together.
    Any thoughts?


    example of the updates.
    ---------------------------------------------------------------
    update1.
    UPDATE dbo_Organization INNER JOIN Phone ON dbo_Organization.ExternalAccountID=Phone.Cust SET dbo_Organization.phone1 = Phone.phone1, dbo_Organization.PhoneType4GUID_FK = {guid {E9DE5F83-0BF0-7148-97E8-2F2FD7EB77AC}}
    WHERE (dbo_Organization.ExternalAccountID=Phone.Cust) And (ISNull(dbo_Organization.phone1) Or dbo_Organization.phone1=' ');
    -----------------------------------------------------------
    update 2
    UPDATE dbo_Organization INNER JOIN Phone ON dbo_Organization.ExternalAccountID=Phone.Cust SET dbo_Organization.phone4 = Phone.fax, dbo_Organization.PhoneType4GUID_FK = {guid {E9DE5F83-0BF0-7148-97E8-2F2FD7EB77AC}}
    WHERE (dbo_Organization.ExternalAccountID=Phone.Cust) And (ISNull(dbo_Organization.phone4) Or dbo_Organization.phone4=' ');
    ---------------------------------------------------------------
    Thanks.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    is this a one time effort as part of a database clean up or consolidation project where you can trigger things manually - or are you trying to set up a recurring task that happens behind the scenes while users are in the application?

    I ask because the answer would differ - - and more importantly if you were to reply that it is the latter - then the normal db reply is you shouldn't be doing what you are attempting....in a normalized relational db one doesn't move or transfer data nor duplicate data... you leave it in the tables it resides and then join tables as needed for presentation of information ....

  3. #3
    Confused is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    2
    NTC,
    I actually created the phone table from a spreadsheet.
    Joined it to a SQL DB just to update the phone #'s in the SQL DB.
    What I want to do is for each cust # in the phone table find the cust # in the SQL DB table and update either or both:
    phone1 if not already populated, with the phone # from the phone table
    phone4 if not already populated, with the fax # from the phone table.
    I can do it with 2 separate update queries, but was trying for a solution of using one update query.
    This will be (I hope) a one time only run.
    Thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM
  2. Replies: 0
    Last Post: 06-03-2009, 10:25 PM
  3. Highlighting Report Data Based on a Condition
    By KramerJ in forum Reports
    Replies: 1
    Last Post: 05-29-2009, 10:27 AM
  4. Replies: 0
    Last Post: 04-17-2008, 09:24 AM
  5. Replies: 1
    Last Post: 10-26-2007, 07:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums