Search for Duplicate two different fields

[terbs]

I know these threads already exist, but I think my problem is slightly different. I have a query which will find duplicate entries for the same field, but I need a query which looks for duplicates across two fields. The problem being the second field (ReturnedSerial) is varchar(max). I get an error, "Can not join on memo, hyperlink field etc". I want to avoid changing the field type at all cost as the database is in SQL replication, meaning I have to take that down and restart to modify a field.

Is there anouther way I can perform a duplicate search for my scenario?

SELECT TBLra.Date, TBLRASub.SERIAL, TBLRASub.DESCRIPTION, TBLRASub.FAULT, TBLRASub.RAID, TBLRASub.RASUBID, TBLRASub.SERIAL, TBLRASub.ReturnedSerial 
 
FROM (TBLra INNER JOIN TBLRASub ON TBLra.RAID = TBLRASub.RAID) INNER JOIN (SELECT ReturnedSerial, COUNT(*) FROM  TBLRASub 
 
GROUP BY ReturnedSerial HAVING COUNT(*) > 1 )  AS d ON TBLRASub.SERIAL = d.ReturnedSerial 
 
WHERE (((TBLRASub.Status) <> 2)) 
 
ORDER BY TBLRASub.SERIAL;

 

[terbs]

i ended up converting the ReturnedSerial field to same data type as Serial. But I still cant seem to get what I need. Does anyone know how to make this work?