Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent an append qry creating duplicates (2003)

    Hi,

    I have a table called TblOUT0506 and TblIncompletes and a query called QryAppendIncompletes. The query appends selected data from OUT0506 to TblIncompletes on a daily basis.

    How do I prevent the query appending data which is already there?

    I have one unquie field to group each record together which is called ReferralID.

    Many thanks
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Prevent an append qry creating duplicates (2003)

    Use a append query and put a WHERE clause in which excludes the records which already exist.

    WHERE ReferralID not in (SELECT ReferralID FROM TblIncompletes)

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent an append qry creating duplicates (200

    Many thanks but I dont understand where to put the where clause, I am trying to put it in the criteria field but am unsure of the exact command Thanks
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Prevent an append qry creating duplicates (200

    Put the Not In .... in the criteria of the referenceid field

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prevent an append qry creating duplicates (200

    Sorry I've been a bit stupid.

    It would have to check several fields, as all can duplicate but not in a group

    The fields are. CRN, APPT ID, APPT TIME, REFID

    Also when I added Not In (SELECT RefID FROM TblIncompletes) it stoped adding any records

    Many thanks
    Regards
    Gerbil (AKA Kevin)

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Prevent an append qry creating duplicates (200

    Were there any records to add?

    You could join both tables on those 4 fields, i presume that the 4 fields are what makes each entry unique (if not what fields make them unique?) then put in a Is Null in one of the fields criteria in TblIncompletes.
    eg
    INSERT INTO TblIncompletes (a,b,c,d,e, etc)
    SELECT a,b,c,d,e, etc FROM TblOUT0506 as Ad INNER JOIN TblIncompletes as To
    ON Ad.a = To.a And Ad.b = To.b (for the 4 fields)
    WHERE To.a Is Null

    This is air code of course

Posting Permissions

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