Results 1 to 3 of 3
  1. #1
    raynman1972 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    31

    Need to Append new/unique records to existing table.

    Hello,

    I am a new user to Access and, despite being an Excel veteran and pretty handy with my MySQL programming, am having a lot of troubles figuring things out so far. I have an existing table that I populated off of a text document and I want to be able to pull in more data from the same file but only insert the rows that are not duplicates- my challenge is that I may not be able to update this data every day and will need to have a script that can insert one or more days worth of data at each iteration. Another challenge that I have is that I do not have any truly unique values.

    What I do have is a combination of Date and Group that will define whether or not a row is unique, basically there can only be one combination of the two:

    Date Group
    2012-05-21 Beta
    2012-05-22 Gamma



    How can I write a simple query that does this test for me and inserts only the new rows? Is there a very basic resource that describes this process really well (I have searched)?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ms Access has some reserved words and I believe Date and Group are in that list.
    see http://allenbrowne.com/AppIssueBadWord.html for the list.

    You could change these fields to MyDate and MyGroup or your choice.

    You could insert your daily /weekly possible append records to a Holding type table, say WorkTable.
    Whatever the name of you ProductionTable is you should make a unique compound index on MyDate + MyGroup (since you said they are unique to each record).

    If you import data from your weekly/daily additions file to your workTable, then a query like

    Code:
    Insert Into ProductionTable( each fleldname, next fieldname,....) From WorkTable
    Where  Worktable.MyDate & WorkTable.MyGroup Not In
    (Select ProductionTable.MyDate & ProductionTable.MyGroup from ProductionTable)
    Make sure you test this on a test version of your database, before running it against your "production copy".

    Also all tables should have a primary key.

    see http://www.techonthenet.com/sql/insert.php for Insert Into syntax

  3. #3
    raynman1972 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    31
    Good stuff, got it to work in pretty quick order. Thanks for the guidance

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

Similar Threads

  1. Replies: 5
    Last Post: 12-12-2011, 08:08 AM
  2. Append table elements as new records
    By bkirsch in forum Forms
    Replies: 1
    Last Post: 11-16-2011, 01:55 PM
  3. Adding records to existing table
    By Mclaren in forum Programming
    Replies: 5
    Last Post: 05-18-2011, 12:44 PM
  4. creating unique ID on existing table
    By TheShabz in forum Access
    Replies: 6
    Last Post: 01-24-2011, 03:53 PM
  5. Replies: 24
    Last Post: 09-01-2010, 02:09 PM

Tags for this Thread

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