Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Exclamation Unanswered: update query in access

    I have a table that has unique ID`s in, and in another table i have the same ID`s in which are linked. What i need to do is update the id if it doesnt appear in one table from another table.
    for eg:
    Code:
    patient_details table:
    
    patient_id
    -----------
         1
         2
         3
    
    extra_notes table:
    
    patient_id
    -----------
          1
          3
    how would i get it to look for the id and if the same dont add, but if the id isnt found add to the table?? so in this case the '2' would be added to the table... Thanks in advance Dave

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by davieboy_xr
    I have a table that has unique ID`s in, and in another table i have the same ID`s in which are linked. What i need to do is update the id if it doesnt appear in one table from another table.
    for eg:
    Code:
    patient_details table:
    
    patient_id
    -----------
         1
         2
         3
    
    extra_notes table:
    
    patient_id
    -----------
          1
          3
    how would i get it to look for the id and if the same dont add, but if the id isnt found add to the table?? so in this case the '2' would be added to the table... Thanks in advance Dave
    Run the Unmatched Record wizard and go from there ...

  3. #3
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    i am doing the code via vb.. so i would have to do it by sql..

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by davieboy_xr
    i am doing the code via vb.. so i would have to do it by sql..
    Geez ... Wrong forum don't you think?????

    But, here's how you set up an unmatched query:

    Code:
    SELECT [Work Orders II].WorkOrderNumber
    FROM [Work Orders II] LEFT JOIN [WO Billing] ON [Work Orders II].WorkOrderNumber = [WO Billing].WorkOrderNumber
    WHERE ((([WO Billing].WorkOrderNumber) Is Null));

  5. #5
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Thumbs up

    im building my app around an access database so i thought this may be the forum but hey.... got it to work
    Thanks alot matey

    Code:
    UPDATE PATIENT_DETAILS LEFT JOIN EXTRA_NOTES ON PATIENT_DETAILS.PATIENT_ID = EXTRA_NOTES.PATIENT_ID SET EXTRA_NOTES.PATIENT_ID = [patient_details].[patient_id]
    WHERE (((EXTRA_NOTES.PATIENT_ID) Is Null));
    Last edited by davieboy_xr; 04-28-04 at 15:51.

Posting Permissions

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