Find field where data varies

[Vexorg]

Hi guys this is my table:

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Query1</caption> <thead> <tr> <th bgcolor="#c0c0c0">Ultimate Trading Name</th> <th bgcolor="#c0c0c0">Ultimate Trading Name ID</th> <th bgcolor="#c0c0c0">Email1</th> </tr> </thead> <tbody> <tr valign="TOP"> <td>INTESA SANPAOLO</td> <td>TN000000682</td> <td>3@palatine.fr</td> </tr> <tr valign="TOP"> <td>INTESA SANPAOLO</td> <td>TN000000682</td> <td>3@palatine.fr</td> </tr> <tr valign="TOP"> <td>INTESA SANPAOLO</td> <td>TN000000682</td> <td>3@palatine.fr</td> </tr> <tr valign="TOP"> <td>INTESA SANPAOLO</td> <td>TN000000682</td> <td>3@palatine.fr</td> </tr> <tr valign="TOP"> <td>COMPAGNIE FINANCIERE SAINT HONORE</td> <td>TN000001869</td> <td>1@bpere.eu</td> </tr> <tr valign="TOP"> <td>COMPAGNIE FINANCIERE SAINT HONORE</td> <td>TN000001869</td> <td>1@bpere.eu</td> </tr> <tr valign="TOP"> <td>ROTSCHILD BANK AG</td> <td>TN000068054</td> <td>1@bpere.eu</td> </tr> </tbody> <tfoot></tfoot> </table>
I want my result set to pull the following because they have the same email but different trading names:

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Query1</caption> <thead> <tr> <th bgcolor="#c0c0c0">Ultimate Trading Name</th> <th bgcolor="#c0c0c0">Ultimate Trading Name ID</th> <th bgcolor="#c0c0c0">Email1</th> </tr> </thead> <tbody> <tr valign="TOP"> <td>COMPAGNIE FINANCIERE SAINT HONORE</td> <td>TN000001869</td> <td>1@bpere.eu</td> </tr> <tr valign="TOP"> <td>COMPAGNIE FINANCIERE SAINT HONORE</td> <td>TN000001869</td> <td>1@bpere.eu</td> </tr> <tr valign="TOP"> <td>ROTSCHILD BANK AG</td> <td>TN000068054</td> <td>1@bpere.eu</td> </tr> </tbody> <tfoot></tfoot> </table>
Is this possible?

 

[Joe4]

You can do this using three separate queries, or one query imbedded with two subqueries.

1st Query/Subquery (Q1) - Perform an Aggregate Query to create all the different Email/Ultimate Trading Name combinations you have

2nd Query/Subquery (Q2) - Perform another Aggregate Query to count how many different Ultimate Reading Name values you have for each email address

Final Query - Link second query back to original data table and return matching records

If the table were named "Table1" and your field names were the same as what you posted, the SQL code for that would look like this:

SELECT
   Table1.[Ultimate Trading Name] 
   , Table1.[Ultimate Trading Name ID] 
   , Table1.Email1
FROM
   Table1
INNER JOIN
(SELECT 
   Q1.Email1
   , Count([Ultimate Trading Name])
FROM
(SELECT 
   Table1.Email1
   , Table1.[Ultimate Trading Name]
FROM 
   Table1
GROUP BY 
   Table1.Email1
   , Table1.[Ultimate Trading Name]) 
   as Q1
GROUP BY
   Q1.Email1
HAVING
   Count([Ultimate Trading Name])>1)
   as Q2
ON
   Table1.Email1=Q2.Email1
ORDER BY
   Table1.[Ultimate Trading Name];

 

[Vexorg]

Joe, thank you! it would perfectly!