Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: Append only unique records

    Hello.

    I was hoping some could show me how I could only append records that are not already in the table. Just the unique records. I have field named SeqOrder that counts number of records (autonumber) but outside of this field if the rest already exist I don't want the record to append to the table. Records are being appended one at a time. I look online for the solution and it seemed like I would have to do a inner join or something but I didn't quite understand it. Here is my sql for my append query.

    Code:
     INSERT INTO Taxable_Facility ( PropertyID, TaxDue, DueDate, PaidDate )
    SELECT [Table1].PropertyID, [Table1].TaxDue, [Table1].DueDate, [Table1].PaidDate
    FROM [Table1]
    WHERE ((([Table1].PropertyID)=[Forms]![Taxable_Facility_Form]![PropertyID]));
    I've tried making it select distinct but that didn't work.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Hi there,

    I would use one of two methods. You can either use SQL that will look for matches on all fields from both tables but where the destination table is missing the ID (auto number) field then append the results of this

    OR

    You could ensure that each record has a unique identifier (other than the auto number field) and simply check for a match on this field rather than on each field.

    Attached is a DB that will demonstrate the first solution. Any unique jobs found in Jobs2 will be appended to Jobs1 by running the Compare append query. This can be adapted to suit your needs. You'll notice in the compare query every field is joined to check for matches whereas if you were to use the second solution you would only have to join on one field (the unique identifier other than auto number) to see if there was a match.

    Hope this helps,

    Dave
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2011
    Posts
    103
    Appreciate it! Though it took me awhile I was able to study your sample database and get mine to work. Really appreciate your help.

  4. #4
    Join Date
    Jan 2012
    Posts
    97
    Your welcome.
    Hope this helps,

    Dave.

    _____________

    Access 2000 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
  •