Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18
  1. #16
    Join Date
    Apr 2017
    Posts
    1,673
    Are you sure they are duplicates? There are no trailing/leading spaces? Only standard ASCII characters are used (there e.g. no different unicode a's or whatever oher characters - a common problem when data from different websites is copied into database fields or form controls)?



    Easiest way to check this: Copy some offending query results into Excel and compare field values from different rows
    Code:
    =(Row1FieldValue = Row2FieldValue)

  2. #17
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    A composite index on 10 fields will prevent users entering exactly the same combination of values in all 10 fields.
    So if you have two records which are identical in 9 of those fields but the tenth field is different, the records are not identical and the composite index will permit both records.

    If you want to ensure there are no duplicate values in any individual field then you need to define each field not to allow duplicates

    Looking at your field names, I would expect fields such as tracking number might have unique values
    However, surely you would expect repetition in most fields e.g. client code, receivers addresses, account number, date fields etc
    Similarly, invoice number could be repeated if an order is split into more than one delivery

    @Arvil
    Not sure I see the point of copying data into Excel to identify duplicates when it can be done easily in Access
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #18
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Another option. If this data is being imported from Excel, you could simly just link the Excel file into an Access table. Then, do an unmatched query between this linked table and the table that you want to import the data into (joining on all the fields you want to consider for duplication). Then, do the Append Query from this Unmatched Query (which will only have new, unique records) to the final table.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-16-2015, 10:15 PM
  2. remove duplicate
    By abraham30 in forum Access
    Replies: 5
    Last Post: 04-23-2015, 04:01 AM
  3. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  4. Remove duplicate but keep the latest record
    By Compufreak in forum SQL Server
    Replies: 2
    Last Post: 06-12-2013, 11:55 AM
  5. Remove Duplicate
    By rgparpaccessforums in forum Access
    Replies: 3
    Last Post: 04-24-2011, 07:24 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