Linking Duplicate ID's

thingssocomplex · May 24, 2011

I am stumped - I have a table from another database, set out like below

FirstName LastName ClientID
Joe Bloggs IB34568
Jan Bloggs IB34568

Ideally within a query I would like to add an additional field named "Joint" and pre-populate with a "Yes" or a "No" not all of the clients within the table will have duplicate Client ID's only joint clients! An example of how I would like the output is below, it does not matter which first name appears, there just needs to be one row; is this possible?

FirstName LastName ClientID Joint
Joe Bloggs IB34568 Yes

Galaxiom · May 25, 2011

Create an aggregate (aka Totals) query. Include the ClientID field twice. Set one to Group By and the other to Count. Add the criteria >1 to the Count instance.

Outer Join this query back to the table and derive a Joint field based on the presence of the record in the first query.

As a subquery structure in SQL:

Code:

SELECT FirstName, Lastname, A.ClientID, IIF(IsNull(IDCount),False,True) AS Joint
FROM TableName AS A 
LEFT OUTER JOIN 
   (
    SELECT ClientID, Count(ClientID) AS IDCount
    FROM Tablename
    GROUP BY ClientID
    HAVING Count(ClientID)>1
   ) AS B
ON A.ClientID = B.ClientID
ORDER BY A.ClientID
;

Of course you aren't goint to store the Joint value in a table since that would breach normalization.

Galaxiom · May 25, 2011

I just noticed you only want one client.
Try this:

Code:

SELECT ClientID, First(FirstName), First(LastName), Joint
FROM 
   (
     SELECT A.ClientID, FirstName, LastName, IIF(IsNull(IDCount),False,True) AS Joint
     FROM TableName AS A 
     LEFT OUTER JOIN
        ( 
         SELECT ClientID, Count(ClientID) AS IDCount 
         FROM Tablename 
         GROUP BY ClientID 
         HAVING Count(ClientID)>1 
        ) AS B
     ON A.ClientID = B.ClientID
     ORDER BY A.ClientID
   ) AS C
GROUP BY ClientID, Joint
;

As a double nested subquery in aircode it might well need some refinement.

Linking Duplicate ID's

thingssocomplex

Registered User.

Galaxiom

Super Moderator

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread