Results 1 to 5 of 5
  1. #1
    krjoshi is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    13

    VBA help needed to update the master table fields from imported table from excel

    Folks,
    Please help me to fix this issue since I am very new to access database. I have a master table called “Justified” and I have an imported table called “ALL JOBS”, now I want to update the master table ”Justified” with some partial fields from imported table.

    I want to update the Fields “Plant” to “MethodStatement” from “ALL JOBS” table to “Justified” table, and also I want to add new record if any found in “ALL JOB”.

    NOTE : Field called “SDJobNo” is the matching field.

    I have attached the Database and picture of what I am trying to accomplish.

    Thanks in advance,



    Joshi
    Attached Thumbnails Attached Thumbnails tblImported.jpg  
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What have you tried? Query? VBA Code?
    What determines whether a record in "ALL JOBS" will/should match a record in "Justified" and be updated?

    and also I want to add new record if any found in “ALL JOBS”.
    Does this mean if a record in “ALL JOBS” is not in "Justified", the record should be inserted into "Justified"?
    What is the criteria to determine whether the record should be added?

  3. #3
    krjoshi is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    13
    Hi Steve,

    Thanks for your reply.

    What have you tried? Query? VBA Code?
    I have tried with Query only.

    What determines whether a record in "ALL JOBS" will/should match a record in "Justified" and be updated?
    For this Q, there is matching field / criteria "SDJobNo" in both tables, if this field matching then I need to update the table "Justified" from "ALL JOBS" table (Only some columns. i.e "Plant" to "MethodStatement" as marked in my first post picture)

    Does this mean if a record in “ALL JOBS” is not in "Justified", the record should be inserted into "Justified"?
    What is the criteria to determine whether the record should be added?
    For this Q, if a record in “ALL JOBS” is not in "Justified", the record should be inserted into "Justified" table. And criteria is, if any new record found in the field "SDJobNo" of "ALL JOBS" and not in "Justified" table.

    Please help me Steve to fix this issue.

    thanks,
    Joshi

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Use query builder to help get the correct SQL, then run the query object or translate it into VBA.

    Those are a lot of fields, I am showing 3.

    UPDATE sql action in VBA:

    CurrentDb.Execute "UPDATE Justified INNER JOIN [ALL JOBS] ON Justified.SDJobNo = [ALL JOBS].[SD Job No] SET Justified.Plant = [All Jobs].[Plant], Justified.Asset = [All Jobs].[Asset], Justified.Unit = [All Jobs].[Unit];"

    INSERT SELECT SQL Action in VBA:

    CurrentDb.Execute "INSERT INTO Justified(Plant, Asset, Unit) SELECT Plant, Asset, Unit FROM [All Jobs] WHERE [SD Job No] IN (SELECT [ALL JOBS].[SD Job No] FROM Justified RIGHT JOIN [ALL JOBS] ON Justified.SDJobNo = [ALL JOBS].[SD Job No] WHERE Justified.SDJobNo Is Null);"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    krjoshi is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2014
    Posts
    13
    Hi June7,
    this is what I am looking for. Thank you so much June7 and Steve for your time and help. The thread is solved now.

    thanks,
    joshi

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

Similar Threads

  1. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  2. Replies: 1
    Last Post: 11-23-2014, 02:20 PM
  3. Update Master Table Baesd on Slave Table
    By alexbeatle in forum Queries
    Replies: 7
    Last Post: 09-21-2014, 01:06 PM
  4. Replies: 3
    Last Post: 01-02-2014, 02:07 PM
  5. Update Master Table with Temporary Table
    By kagoodwin13 in forum Programming
    Replies: 2
    Last Post: 10-15-2013, 11:59 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