Results 1 to 4 of 4
  1. #1
    jset818 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    38

    Question How to prevent append query from adding duplicates to table

    Hi everybody, I'm trying to create an error report for our company. Certain project/program info might be incorrect in our system and the report is designed to list all those errors. The issue I'm having is that the append query doesn't recognize when the error is already in the table/report. So every week when I run the append query, it keeps adding those same duplicate errors over and over again.



    I have attached a screenshot of the report to this thread. The projects are labeled by it's own ID with the error alongside with it. You can see the error "Hotel Invoice not Received" appears multiple times for Project OV10489. The only thing different is "Date Identified" which is just the date that I ran the report.

    Click image for larger version. 

Name:	KPI QC duplicates.JPG 
Views:	11 
Size:	61.3 KB 
ID:	22010

    Is there a way to set up a filter that prevents the query from adding duplicates to the table if the data is already on the table?
    I'm still a beginner with Access. I don't work in SQL view, I use design view almost entirely. Please explain it in the most simplest way.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    to eliminate duplicates suggest you remove the '1' column and group by projectID and error, then use first/last/max /min as required for date identified.

    Note that 'Error' is a reserved word - using it can result in inexplicable errors so recommend you change it to something else. Using 1 as a field name can also cause problems

    Here is a link to reserved words

    https://support.office.com/en-in/art...7-da237c63eabe

  3. #3
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    The way I do it is in the Query your using to append your records add the table your appending to. Use a left outer join. Look at the sample

    Code:
    Insert Into table1
    Select table2.ProjectID, table2.Error,Now() as [Date identified]
    From table2 left outer join table1 on table2.projectID = table1.projectid and table2.error = table1.error
    Where table1.projectid is null
    table1 in the sample above is the data you're appending. table2 is the table your appending too.
    the left outer join is linked on both the projectID field and the error field because you can have multiple errors with the same projectID so you must check the error as well.

  4. #4
    jset818 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    38
    Quote Originally Posted by Ajax View Post
    to eliminate duplicates suggest you remove the '1' column and group by projectID and error, then use first/last/max /min as required for date identified.

    Note that 'Error' is a reserved word - using it can result in inexplicable errors so recommend you change it to something else. Using 1 as a field name can also cause problems

    Here is a link to reserved words

    https://support.office.com/en-in/art...7-da237c63eabe
    The "1" column is just excel labeling the rows.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append query without duplicates using two id's
    By sdel_nevo in forum Queries
    Replies: 2
    Last Post: 02-20-2015, 08:25 AM
  2. Replies: 6
    Last Post: 10-23-2013, 08:06 AM
  3. Replies: 2
    Last Post: 02-28-2013, 07:00 PM
  4. Append query is creating duplicates
    By D4WNO in forum Database Design
    Replies: 3
    Last Post: 12-10-2012, 10:47 AM
  5. Append query creating duplicates
    By dhicks19 in forum Queries
    Replies: 1
    Last Post: 05-14-2012, 06:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums