Results 1 to 3 of 3
  1. #1
    drh is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Posts
    5

    Post INSERT query: insert new data only

    Dear all,

    Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.

    TBL_NEWDATA { Person_ID, Department_ID }
    TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }

    I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there.

    i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.

    INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
    SELECT Person_ID, Department_ID
    FROM TBL_NEWDATA
    WHERE TBL_NEWDATA.Department_ID='Form...'
    AND NOT EXISTS
    (
    SELECT Person_ID, Department_ID
    FROM TBL_PERSON_ALLOCATIONS
    WHERE TBL_PERSON_ALLOCATIONS.Department_ID='Form...'
    )

    This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.

    Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.

    Your advice is very much appreciated as I am drawing a blank.

    Best

    David

  2. #2
    drh is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Posts
    5
    I've solved my own problem! I was using multiple columns in the subquery; removing one fixed the issue. Thanks to those that looked, anyway!

  3. #3
    TPCinLV is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    1
    Hi David,
    Would you post your solution please?
    I have a similar problem and I think your solution may be what I need.

    I'm just getting back into using ACCESS SQL after a very long absence and am finding I've forgotten a lot.
    I really need to INSERT INTO a file and keep getting syntax errors. My gut tells me this should be simple and I must be overlooking something obvious.
    My current query is:
    SELECT *
    INSERT INTO Charges
    FROM HoldData
    WHERE HoldData.Condition<>"Monthly";

    Which is so bad I can't even save it. The structure of both files is identical.

    Thanks,
    Pam

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

Similar Threads

  1. Access insert into query...
    By xmetisx in forum Queries
    Replies: 2
    Last Post: 04-06-2010, 02:27 PM
  2. Just insert new data
    By watzmann in forum Access
    Replies: 1
    Last Post: 11-29-2009, 11:48 AM
  3. Attachment data type INSERT INTO not working
    By LouisLouis in forum Programming
    Replies: 0
    Last Post: 09-27-2009, 02:23 PM
  4. Insert Query output into a table
    By ammu_sridhar in forum Programming
    Replies: 1
    Last Post: 06-12-2009, 01:09 AM
  5. Avoid jump to first data after insert new picture
    By gigolomoden in forum Programming
    Replies: 0
    Last Post: 08-04-2008, 10:08 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