Find duplicate values with different year in SQL

Question

How can I search duplicate records in a table but has different years.

My sample data:

Cus_No  Item_No Ord_Dt  Orders 
1       A       2016    1
1       A       2017    2
1       B       2016    1
2       B       2015    1
2       B       2018    1

Output needed

Cus_No  Item_No Ord_Dt  Orders 
1       A       2016    1 
1       A       2017    2
2       B       2015    1
2       B       2018    1 

I am trying to collect all records with the same Cus_No, the same Item_No that has any value in Orders and exist in any year in Ord_dt. The reason is, I need to find those items with the same customer number that has orders from all years. I am using MS Query and this is the SQL statement I tried but still displays all records.

SELECT `'table'`.Cus_No, `'table'`.Item_No, `'table'`.Ord_Dt, `'table'`.Orders
FROM `'table'`
WHERE (`'table'`.Orders>=1) AND (`'table'`.Ord_Dt In ('2016','2017'))

Answer 1

Assuming you want to identify records corresponding to customer items which appeared across more than one year, we can try the following:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT Cus_No, Item_No
    FROM yourTable
    GROUP BY Cus_No, Item_No
    HAVING COUNT(DISTINCT Ord_Dt) > 1
) t2
    ON t1.Cus_No  = t2.Cus_No AND
       t1.Item_No = t2.Item_No