Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    36

    Question Unanswered: Append Query to append only distinct records

    I would like to add values to one table based on entries in another table. However, if a combination of field1 and field2 already exist in the first table, I would not like to add that record. I have tried to do an append query with a NOT IN subselect, but it doesn't seem to be the right approach. Can anyone give me guidance on this?

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Append Query to append only distinct records

    Query DistinctData

    SELECT tblNewData.*
    FROM tblNewData LEFT JOIN tblHistory ON tblNewdata.Field1 = tblHisory.Field2
    WHERE tblHisory.ID Is Null;

    Query AppendData

    Insert into tblHistory (field1,field2,field...)
    Select field1,field2,field...
    from DistinctData;

  3. #3
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    >if a combination of field1 and field2 already exist in the first table...

    I think Robt917's sql needs to be:

    SELECT tblNewData.*
    FROM tblNewData LEFT JOIN tblHistory ON (tblNewdata.Field1 = tblHisory.Field1) AND (tblNewdata.Field2 = tblHisory.Field2)
    WHERE tblHisory.ID Is Null;

Posting Permissions

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