Results 1 to 7 of 7
  1. #1
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80

    Removing Duplicates based on single field.

    Is there a way to remove duplicates from a report based on a single field in the report? I know I can set it to not include uniques on a single query but the problem is this report uses a Union Query made up of about 10 queries and some records hit more than 1 of these queries so they are showing up on the report multiple times. One field in each record is different based on the query it came from (a text box is filled out with information depending on which query its from).

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    How about creating a make table query from the Union Query and then applying the concepts in this video to achieve your results.

    http://www.datapigtechnologies.com/f...teproblem.html

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or

    SELECT DISTINCT *
    FROM (your union query here)

  4. #4
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    If I do the SELECT DISTINCT * it still gives me all of the records (doesnt remove any). If I specify SELECT DISTINCT ConsultNumber (ConsultNumber is the one field I do NOT want repeated) then it does remove the repeats, but the query only displays the ConsultNumber field, not all of the fields that I require. Maybe I'm just missing something small.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    keep adding fields to select distinct, when you start getting duplicates, that is the field to need to not include

  6. #6
    Ekhart is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    80
    I know which field is not distinct, the problem is I still want that field to show on the report.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post your table design and explain what field is the issue.
    If you have duplicates --real duplicates - then you have an issue.
    If you want ot remove duplicates, that is have a table with unique values -normally done by means of a properly constructed primary key -then you can create a new table design, identify the field that you want to have no duplicates and state so in the properties; then append your current table to this new table design and ACCESS will not add the duplicates.

    If your issue is different, then please tell us more.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-14-2015, 06:38 PM
  2. Removing Duplicates
    By hefferwolfe in forum Queries
    Replies: 1
    Last Post: 04-30-2014, 04:31 AM
  3. Merging & removing duplicates
    By Johin.b in forum Access
    Replies: 1
    Last Post: 08-09-2013, 03:07 PM
  4. Removing duplicates
    By DAVID W in forum Access
    Replies: 5
    Last Post: 12-21-2011, 03:15 PM
  5. Need help removing duplicates
    By warlock in forum Queries
    Replies: 1
    Last Post: 04-14-2011, 03:44 PM

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