Originally Posted by
Gicu
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