Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: Help with Append Query with Access 2000

    Hi,

    I am using a query to copy data to another table and need a bit of help.

    I am copying data from the read-only Table A to Table B (so I can alter it as its needed and keep table A intact) with an Append Query.

    But how can I have the query ONLY copy the data to Table B that hasn't already been copied.

    Any ideas?

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    By default, that is Access' behavior.

    In other words, if TableA has 100 records (50 of which were recently added) and TableB has 50 records (the 50 previous records from TableA), then, if you do an append query from TableA to TableB, it will not overwrite the original 50 records, but simply append the 50 new records into TableB.

    This is a very nice feature of Access. SQL Server does not behave in this manor and I prefer Access' behavior.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Jan 2004
    Posts
    5
    Originally posted by PracticalProgram
    By default, that is Access' behavior.

    In other words, if TableA has 100 records (50 of which were recently added) and TableB has 50 records (the 50 previous records from TableA), then, if you do an append query from TableA to TableB, it will not overwrite the original 50 records, but simply append the 50 new records into TableB.

    This is a very nice feature of Access. SQL Server does not behave in this manor and I prefer Access' behavior.
    In my testing it did not do this (I'm using Access XP in Access 2000 file mode).

    Say I have 50 records and ran the query, Table B would have 50 records. Say I added 10 records to Table A and ran the query again, it adds the 60 records from Table A, giving me 110 in Table B and duplicates.

    What I want to do, is run the Append Query for only those records that have not already been copied.

    [EDIT] If I set the target field to 'No Duplicates', the query returns with key violation errors for the records that have already been copied and you can either run the query (and duplicate items) or cancel it altogether.
    Last edited by kkir; 04-05-04 at 20:56.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by kkir
    In my testing it did not do this (I'm using Access XP in Access 2000 file mode).

    Say I have 50 records and ran the query, Table B would have 50 records. Say I added 10 records to Table A and ran the query again, it adds the 60 records from Table A, giving me 110 in Table B and duplicates.

    What I want to do, is run the Append Query for only those records that have not already been copied.

    [EDIT] If I set the target field to 'No Duplicates', the query returns with key violation errors for the records that have already been copied and you can either run the query (and duplicate items) or cancel it altogether.
    I'm running 2003 now but just switched over recently. I just set up a similar format to what you are describing and ran the query. It will give you the message with key violations for the records that are already there, but in my case, it only added the additional records.

    Does that sound like what you want to happen?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by kkir
    In my testing it did not do this (I'm using Access XP in Access 2000 file mode).

    Say I have 50 records and ran the query, Table B would have 50 records. Say I added 10 records to Table A and ran the query again, it adds the 60 records from Table A, giving me 110 in Table B and duplicates.

    What I want to do, is run the Append Query for only those records that have not already been copied.

    [EDIT] If I set the target field to 'No Duplicates', the query returns with key violation errors for the records that have already been copied and you can either run the query (and duplicate items) or cancel it altogether.
    Ok, I had another thought.

    Set up a find unmatched query between the two tables that will only show which rows are in table 1 that are not in table 2.

    Now, use this as the source for the Append query and append away.

    Whadaya think?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  6. #6
    Join Date
    Jan 2004
    Posts
    5
    Originally posted by basicmek
    Ok, I had another thought.

    Set up a find unmatched query between the two tables that will only show which rows are in table 1 that are not in table 2.

    Now, use this as the source for the Append query and append away.

    Whadaya think?
    This is a good solution.

    I did a bit more checking: If I make the target field indexed, the query returns with key violations errors. If you continue and ignore the errors it adheres to the index and only copies those that haven't already been copied.

    As its for a basic end user, your solution is more friendly to the user.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •