Results 1 to 7 of 7
  1. #1
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86

    Finding duplicate entries with a unique 2nd field

    Hi
    I have a table which I need to extract rows where the product appears more than once but the bin number needs to be unique, so for example my table would have the following fields and values…

    product | bin_number
    PRODUCT1 | 1AA01A1
    PRODUCT2 | 1BA02B2
    PRODUCT3 | 1CC04B4
    PRODUCT3 | 1CC04B4
    PRODUCT3 | 1CC04B4
    PRODUCT4 | 1DD01B5
    PRODUCT5 | 1DE03C2
    PRODUCT5 | 1DB06C2
    PRODUCT5 | 1DB06C2
    PRODUCT5 | 1AB06C8

    PRODUCT3 appears more than once but it has the same bin number on every row where as PRODUCT5 appears 4 times but 3 of the bin numbers are different. My result needs to show PRODUCT5 but only the unique bin numbers so it should only show 3 results.
    I’ve been trying to use SELECT Count(*) and HAVING Count(*) > 1 in my queries but this only works when I’m taking one field into consideration, I’m taking 2 fields into consideration so I need something different.



    Can someone help me please?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You could make a temp table keyed on Bin#.
    append the recs to it,then run a dupe query off this table.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try:

    SELECT DISTINCT tblProduct.Product, tblProduct.Bin_Number
    FROM tblProduct INNER JOIN tblProduct AS tblProduct_1 ON tblProduct.Product = tblProduct_1.Product
    WHERE (((tblProduct.Bin_Number)<>[tblProduct_1].[Bin_Number]));

    Cheers,
    Vlad

  4. #4
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Quote Originally Posted by Gicu View Post
    SELECT DISTINCT tblProduct.Product, tblProduct.Bin_Number
    FROM tblProduct INNER JOIN tblProduct AS tblProduct_1 ON tblProduct.Product = tblProduct_1.Product
    WHERE (((tblProduct.Bin_Number)<>[tblProduct_1].[Bin_Number]));
    Thanks Vlad your suggestion works but I have a couple a curve balls which I didn't foresee in my original post. My data now looks like this...

    warehouse | product | bin_number
    W1 | PRODUCT1 | T2401
    W1 | PRODUCT1 | 1AA01A1
    W1 | PRODUCT2 | 1BA02B2
    W1 | PRODUCT3 | 1CC04B4
    W1 | PRODUCT3 | 1CC04B4
    W1 | PRODUCT3 | 1CC04B4
    W1 | PRODUCT4 | 1DD01B5
    W2 | PRODUCT5 | 1AB06C8
    W1 | PRODUCT5 | 1DE03C2
    W1 | PRODUCT5 | 1DB06C2
    W1 | PRODUCT5 | 1DB06C2
    W1 | PRODUCT5 | L5432
    W1 | PRODUCT6 | 1AA04C2
    W1 | PRODUCT6 | 1AB06C3
    W2 | PRODUCT6 | 1CC01C3
    W2 | PRODUCT6 | 1CC01C3
    W2 | PRODUCT6 | 1CC05C2

    I need to run this query on products that are in warehouse W1 only and only take into considerations bin_number beginning with 1, I tried this query...
    Code:
    SELECT DISTINCT tblProduct.product, tblProduct.bin_numberFROM tblProduct INNER JOIN tblProduct AS tblProduct_1 ON tblProduct.Product = tblProduct_1.Product
    WHERE (((tblProduct.bin_number)<>tblProduct_1.Bin_Number) And ((tblProduct.warehouse)="W1") And ((tblProduct.bin_number) Like "1*"));
    The problem with this is that PRODUCT1 shows up in the result even though it only has one row with a bin_number beginning with 1.

    I've attached a database with my example.
    Duplicate Entries Unique 2nd Field.zip

    Thanks

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you go!

    Cheers,
    Vlad
    Attached Files Attached Files

  6. #6
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Thanks Vlad, just the job. Works perfectly now, very much appreciated.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome!

    Cheers,
    Vlad

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

Similar Threads

  1. Duplicate entry in unique field in table.
    By kkbharat in forum Access
    Replies: 5
    Last Post: 03-22-2018, 12:17 AM
  2. Replies: 10
    Last Post: 02-07-2018, 10:00 AM
  3. Replies: 3
    Last Post: 09-26-2017, 11:19 PM
  4. Replies: 2
    Last Post: 02-28-2013, 10:06 AM
  5. Replies: 4
    Last Post: 10-11-2012, 07:40 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