Results 1 to 3 of 3
  1. #1
    inhops is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    13

    Ignore duplicates in one field only

    We have a database where we import up to 20 tables all of which will most likely have duplicate records (all fields are the same on some records). Using the Totals with Group by & First in queries has worked fine up until now. Recently we have come across the situation where the SKU field has a duplicate, but the Price field is different in the two records. So this SKU ends up listed twice in the resulting MakeTable. Is there something that we can do in the query to treat these two records as duplicates (and remove one) even though all fields are not the same? In this DB, we do only have these two fields in the import files. Thanks for your help.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If I were addressing this problem I do not believe I would ignore the fact that there are two prices for a single SKU. I would probably create a Totals query and use the Count method to see if there was a count greater than one. With the duplicate records isolated, I would apply logic to manage the duplicates.

  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    I agree with Itsme, but if the different prices are valid, you'll have to remove the price columns from the groupby or find another way of account for it (perhaps use first, last, max, min etc)

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

Similar Threads

  1. Calculated Field - No duplicates
    By noaccessguru in forum Access
    Replies: 2
    Last Post: 08-18-2013, 10:47 PM
  2. Need design suggestions for indexed-no duplicates field
    By jax1000rr in forum Database Design
    Replies: 3
    Last Post: 01-29-2013, 06:42 AM
  3. Replies: 3
    Last Post: 02-10-2012, 11:34 AM
  4. No duplicates on composite field.
    By boundfree in forum Access
    Replies: 15
    Last Post: 09-06-2011, 02:28 PM
  5. Replies: 2
    Last Post: 08-17-2010, 02:58 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