Results 1 to 4 of 4
  1. #1
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    208

    Append Query - Add a new record or update an existing record

    I have 2 tables. A company table and a company personnel table.



    The company personnel table contains a foreign key to the company table.

    I want to replicate the records entered in these 2 tables, into 2 other tables for a different company.

    As each record is entered, I can trigger an append query to update the second set of tables.

    In the append for Company, I would leave out the ID which is an autonumber field.

    Question 1. Could I check that the company I am appending is not already in the tables I am appending to and if it is update it rather than create a new record.

    I would expect the Company Name to be unique.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,226
    Why do you need two more tables? Why not just use the existing tables?

    Yes, code can look for existing data and take an action depending on what is found. This could be an If Then Else construct in macro or VBA.
    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.

  3. #3
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    208
    Why do I need two more tables is a good question. I designed a part of the system and now I'm not quite sure how I got there. I need two different entities, but the data feeding them could be the same. I have a bound form and have introduced 8 subforms. I choose from the 8 different table with combo boxes. So at the moment if I try to pull from the same table for 2 of them, I get the same value populating both subforms. And so far I haven't found a way round that.

  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
    49,226
    I don't understand the data structure nor the form behavior.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

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

Similar Threads

  1. Replies: 7
    Last Post: 06-05-2015, 11:13 AM
  2. Update attachment field in existing record
    By incipientclarity in forum Programming
    Replies: 5
    Last Post: 05-28-2014, 07:20 AM
  3. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  4. Replies: 3
    Last Post: 08-28-2012, 02:27 PM
  5. Replies: 0
    Last Post: 02-25-2011, 09:40 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 - Senior Forums