Finding a count of duplicates

[Pwyd]

So i'm having some trouble with this table, and its query. I'd like to find
a count of the number of duplicates, by a Supplier field. Unfortunately, not
all of the entries in it are precisely the same. How would one find the
number of duplicates that occur by say, the first 5 characters of the
supplier name in that field?

here's the query i was using as to count:

SELECT Mail_Log_Data.Supplier, Mail_Log_Data.ID,
Mail_Log_Data.InvoiceNumber, Mail_Log_Data.InvoiceDate,
Mail_Log_Data.PONumber, Mail_Log_Data.InvoiceAmount, Mail_Log_Data.BatchDate,
Mail_Log_Data.ObUNID
FROM Mail_Log_Data
WHERE (((Mail_Log_Data.Supplier) In (SELECT [Supplier] FROM [Mail_Log_Data]
As Tmp GROUP BY [Supplier] HAVING Count(*)>1 )))
ORDER BY Mail_Log_Data.Supplier;

As i said before. some of the supplier field names say

Company name, and other entries in the field are Company Name (system
message) which are somewhat unique. So its showing me several groups of
duplicates, when i'd really just like to know how many are duplicates where
company name, and company name, logo mean exactly the same thing in the
Supplier field. How would i do that?

 

[Piet Linden]

So i'm having some trouble with this table, and its query.  I'd like tofind
a count of the number of duplicates, by a Supplier field.  Unfortunately, not
all of the entries in it are precisely the same.  How would one find the
number of duplicates that occur by say, the first 5 characters of the
supplier name in that field?

Use LEFT([YourField],5) and then do a count on that result.

 

[Pwyd]

Do you mean use this as the field's data?


Blah:LEFT([Supplier],5)

count

1

?

So i'm having some trouble with this table, and its query. I'd like to find
a count of the number of duplicates, by a Supplier field. Unfortunately, not
all of the entries in it are precisely the same. How would one find the
number of duplicates that occur by say, the first 5 characters of the
supplier name in that field?

Use LEFT([YourField],5) and then do a count on that result.

 

[Ron2006]

Yes

 

[John Spencer]

SELECT Mail_Log_Data.Supplier, Mail_Log_Data.ID,
Mail_Log_Data.InvoiceNumber, Mail_Log_Data.InvoiceDate,
Mail_Log_Data.PONumber, Mail_Log_Data.InvoiceAmount,
Mail_Log_Data.BatchDate,
Mail_Log_Data.ObUNID
FROM Mail_Log_Data
WHERE Left(Mail_Log_Data.Supplier,5) In
(SELECT Left([Supplier],5)
FROM [Mail_Log_Data] As Tmp
GROUP BY Left([Supplier],5)
HAVING Count(*)>1 )
ORDER BY Mail_Log_Data.Supplier;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================