btamulis 2/25/2022 9:56:00 AM

SQL - Finding the duplicate values in a Field

We use the comment1 field in the RM00101 (Customer Master Table) to store a customer identifier from a separate external billing system.

Example: CUSTID in GP =HOME345. Comment1 in GP for HOME345=589632.

We have to be certain 589632 is not stored in RM00101 for any other CUSTID. 

Therefore, in the GP RM00101 Table I need to query and find any customers with duplicate comment 1 values. We have over 46,000 CustID's in the table. We know we occasionally enter a duplicate value. 

Basically, our business requires a one-to-one relationship between GP Customer ID and the Comment 1 field. However, the comment 1 field is not designed to enforce the business requirement, so we need to see if we have any duplicate comment 1 field values.

Thanks in advance, 

Bron

Version: All
Section: Dynamics GP, SQL Scripts


Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3