CODEWITHSUNDEEP
sqlms-access
ckey
ckey

Reputation: 31

Access query/SQL - duplicates in one field with distinct multiple 2nd field

I am working on a database with products and lot numbers. Each entry in the Lots table has a Lot Number and a Product description.

Sometimes there are multiple records of the same lot number, for example when an item is repacked a new record is created, but with the same Lot Number and same product description - this is fine. But other times there are problem cases, namely when two different products share the same Lot Number. I am trying to find those.

In other words, there are 3 possibilities:

  1. Lot numbers for which there is only one record in the table.
  2. Lot numbers for which there are multiple records, but the Product description is the same for all of them
  3. Lot numbers for which there are multiple records, and the product descriptions are not all the same.

I need to return only #3, with a separate record for each instance of that Lot Number and product description.

Any help would be greatly appreciated.

enter image description here

Thanks Juan for the sample data. Using this example, I want to return the data contained in Id 2-8, but not 1, 9, 10, 11.

Upvotes: 0

Views: 2142

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

This wasn't easy because lot of time don't use access.

  • First select unique values using distinct.
  • Then count how many diferent product appear on each lotnumber using group by
  • Last join both result and show only the lots with more than one description where total >1

.

SELECT id, Product.lotnumber,  Product.Product, total
FROM 
    Product Inner join
    (
    SELECT lotnumber, count(*) as total
    FROM 
        (SELECT distinct lotnumber, product
        FROM Product)
    GROUP BY lotnumber
    ) SubT  On Product.lotnumber = SubT.lotnumber
WHERE total > 1
ORDER BY id

As you can see :

  • lot 2 have two products (yy and zz)
  • lot 3 have thre products (aa, bb, cc)

enter image description here

I include my product table:

enter image description here
Sorry for spanish. Field types are Autonumeric, Short Text, and Number

Upvotes: 3

Related Questions