Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Unanswered: Append Queries - I dont know much about them

    I figured my best solution to a previous post was to use an append query.

    My append query post info, to a table called archive, which is great.

    The query picks up a few new records and i run it again, i get an error msg:

    "4 Records were added to table, 20 records were not added....."

    Is there anyway, that when the append query has run it can somehow compare with the table and ONLY add records not already in the table (say by unique ID) and to update itself with only new records and if a record is in the archive tabl;e to remove the other recrods, or soemthing not to thrwo out an error messgae??

    any tips would be wonderful as always

    thanks dan
    sometimes simple is best.... and i'm just a simple fellow.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure on this as it's been a while but can't you make an unmatched query between the two tables comparing the unique ID (i.e. via the "Find Unmatched query wizard") and then change it to an append query?
    Last edited by pkstormy; 05-21-07 at 19:23.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    I had an idea, not sure if it would work, to run a delete query right after to remove the data from the original source, then it wouldnt appear in the append query the 2nd time around??!?!
    sometimes simple is best.... and i'm just a simple fellow.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That's more a question for you than for us. It's certainly a viable option, but only you know whether the data needs to remain in the original table or not.
    Paul

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you sure you want to mess around with deleting data from the original source?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I tried testing the "Find unmatched query wizard" and then changing it to an append query and it seems to work.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Actually it works very well (I recall now that I've done this before without any problems.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Im not sure if that would work in my case,

    the original source is tbl_transactions and then the append query picks it up and appends to tbl_archive, the data in tbl_transactions and sub table tbl_sub_trans, are really arbitary figures, used 'on the day' and the tbl_archive would be used for accountacny, thanks for the advice, i'll try it
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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