Select Distinct query to remove duplicates

[Guest]

I am trying to run a Select Distinct query on a mass of address records. I
am only wanting to query out duplicate "contact names", here is an SQL
statement I am using , the problem is this is query out only identical
duplicate records. I need to query out only identical duplicate "contact
names" and be able to see the entire records for each remaining.

I think I just need to move the DISTINCT statement.

SELECT DISTINCT [TRIO 2005 Mailing List].[Contact Name], [TRIO 2005 Mailing
List].[Company Name], [TRIO 2005 Mailing List].[Address], [TRIO 2005 Mailing
List].[City], [TRIO 2005 Mailing List].[State], [TRIO 2005 Mailing List].[Zip]
FROM [TRIO 2005 Mailing List];

 

[John Spencer (MVP)]

No, you don't need to move the DISTINCT. It only goes in one place. What you
probably need is a totals query.


SELECT First([T.[Contact Name]) as OneContactName,
[T].[Company Name], [T].[Address],
[T].[City], [T].[State], [T].[Zip]
FROM [TRIO 2005 Mailing List] AS T
GROUP BY [T].[Company Name], [T].[Address],
[T].[City], [T].[State], [T].[Zip]

That will return one record for each combination of [T].[Company Name],
[T].[Address], [T].[City], [T].[State], and [T].[Zip]. It will grab the first
Contact Name it sees for each combination. If that doesn't do what you want,
you need to be more specific in the results you desire. Perhaps entring three
or four records and then showing what you would expect to get back from the query.

 

[Guest]

Thanks John, will give this a shot today and let you know if it returns the
results I am looking for.

No, you don't need to move the DISTINCT. It only goes in one place. What you
probably need is a totals query.


SELECT First([T.[Contact Name]) as OneContactName,
[T].[Company Name], [T].[Address],
[T].[City], [T].[State], [T].[Zip]
FROM [TRIO 2005 Mailing List] AS T
GROUP BY [T].[Company Name], [T].[Address],
[T].[City], [T].[State], [T].[Zip]

That will return one record for each combination of [T].[Company Name],
[T].[Address], [T].[City], [T].[State], and [T].[Zip]. It will grab the first
Contact Name it sees for each combination. If that doesn't do what you want,
you need to be more specific in the results you desire. Perhaps entring three
or four records and then showing what you would expect to get back from the query.

I am trying to run a Select Distinct query on a mass of address records. I
am only wanting to query out duplicate "contact names", here is an SQL
statement I am using , the problem is this is query out only identical
duplicate records. I need to query out only identical duplicate "contact
names" and be able to see the entire records for each remaining.

I think I just need to move the DISTINCT statement.

SELECT DISTINCT [TRIO 2005 Mailing List].[Contact Name], [TRIO 2005 Mailing
List].[Company Name], [TRIO 2005 Mailing List].[Address], [TRIO 2005 Mailing
List].[City], [TRIO 2005 Mailing List].[State], [TRIO 2005 Mailing List].[Zip]
FROM [TRIO 2005 Mailing List];