counting duplicates

[Guest]

HI,

I have conducted a "find duplicates" query in order to find records where
entries in some, but not all, of the fields duplicate. For example, for
records A and B Fields 1, 2, and 3 may have the same entry, but Field 4 may
not. I don't care, because I am just looking to find if records duplicate in
Fields 1, 2, and 3. The "find duplicates" query allow me to do this. However,
I would like a count of duplicates, i.e. how many of each duplicate is there.
I have read on this forum about a "totals query" but can't figure it out.


Thanx

 

[Guest]

Try this

SELECT TableName.A, TableName.B, Count(TableName.B) AS CountB
FROM TableName
GROUP BY TableName.A, TableName.B
HAVING Count(TableName.B)>1

 

[Guest]

Hi,
My "find duplicates" query resulted in over 7000 records, so there are a
lot more than two records (A and B). Or are you saying that A and B in your
reply are field names?? NOw that I look at it I think you are. I'll give it a
try..

 

[Guest]

Yes, A and B are field name that you want to group on.
If you want to group on three field, then try this
SELECT TableName.Field1, TableName.Field2,TableName.Field3,
Count(TableName.Field3) AS CountField3
FROM TableName
GROUP BY TableName.Field1, TableName.Field2,TableName.Field3
HAVING Count(TableName.Field3)>1

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck