Results 1 to 6 of 6
  1. #1
    joannakf is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    11

    Update/Append Query

    I am trying run a update/append query so when the records match it will update the location and then add when the record doesn't exist yet. I apoligive for the messy table below but I keep getting an eror message trying to upload a excel file.



    Table 1 Table 2 Table 1 becomes
    Name Code Building Floor Name Code Building Floor Name Code Building Floor
    Doe, John 12321 DE 2 Smith, Steve 432342 ME 2 Doe, John 12321 DE 2
    Smith, Steve 432342 DE 3 Johnson, Jim 13432 ME 3 Smith, Steve 432342 ME 2
    Allen, Joe 454 ME 2 Johnson, Jim 13432 ME 3
    Allen, Joe 454 ME 2
    What changed

  2. #2
    TheShabz is offline Court Jester
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    1,368
    Is the issue on the excel file import or are you having issues with the logic to update your table? both?

  3. #3
    joannakf is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    11
    The logic to update table 1

  4. #4
    TheShabz is offline Court Jester
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    1,368
    Is your Code field unique?

  5. #5
    joannakf is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    11
    the name field is

  6. #6
    TheShabz is offline Court Jester
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2010
    Posts
    1,368
    Ouch. Something to consider for future use. What if you have two people with the same name?

    Anyway, here's how I'd go about it:
    Assumptions:
    1. Table1 is the destination table that is in Access
    2. Table2 is the excel sheet that you will be checking against
    3. You're comfortable in VBA
    4. You can turn pseudo-code into working code
    5. The fields in your tables are the only ones in the actual tables.

    Method:
    1. Import the excel sheet into Acces (transferspreadsheet, acimport, etc)
    2. add 2 columns to Table2. 1 called inTable1 and another called isChanged. both are either number or yes/no datatypes)
    3. Run a query SELECT Table1.Name INTO junkTable FROM Table1 WHERE Table1.Name = Table2.Name
    You now have a table with the names that are already in Table1.
    4. update inTable1 with UPDATE Table2 SET inTable1 = -1 WHERE Table2.Name = junkTable.Name
    5. Find which are unchanged by SELECT Table1.Name INTO junkTable2 FROM Table1 WHERE (each field from Table1 = each field from Table2)
    6. Update isChanged with UPDATE Table2 SET isChanged = -1 WHERE Table2.Name Not In (SELECT junkTable2.Name From junkTable2)
    7. You now have a field in Table2 letting you know that it is in Table1 already and one that lets you know that a record is changed. All you need to do now is Append to Table 1 from Table 1 where inTable1 <> -1 for the new records and UPDATE Table1 SET each record = its corresponding record in Table2 WHERE isChanged = -1

    That should be it. Let me know if any of that was difficult to follow. This is by no means the only way to do it and I'm sure there's someone else out there who knows a much snazzier way.

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

Similar Threads

  1. Creating an Update/Append Query
    By Jray9242 in forum Import/Export Data
    Replies: 2
    Last Post: 04-23-2012, 06:31 PM
  2. Replies: 3
    Last Post: 03-11-2012, 03:35 PM
  3. Update or Append Query
    By Ran in forum Access
    Replies: 8
    Last Post: 01-06-2012, 12:15 PM
  4. An integrated Append + Update query ?!
    By cement in forum Queries
    Replies: 3
    Last Post: 12-31-2010, 11:01 AM
  5. Append/Update Query -- Need Help!
    By su-san in forum Queries
    Replies: 12
    Last Post: 11-08-2010, 12:52 PM

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