Find, List and Count Duplicates in 4 different columns

[thousman]

In advance, Thank You All as I appreciate any assistance. Also, if you can recommend any GOOD, FREE training that may be available I would appreciate it!



So I have a database with 180,023 Records. I want to list and count All Duplicates in 4 different fields or (columns)
The Table Name is "All Accounts Since 2013"
Fields I want to search for Duplicates are "Patron Account Name, Primary Phone Display, Primary Email Address and Primary Address"

Found this

SELECT LastName, FirstName, Institution, Sum(1) as CNT
FROM MyTableName
GROUP BY LastName, FirstName, Institution
HAVING (Sum(1) > 1)


which I wrote like this

SELECT Patron Account Name, Primary Phone Display, Primary Email Address, Primary Address 1 Sum(1) as CNT
FROM All Accounts Since 2013
GROUP BY Patron Account Name, Primary Phone Display, Primary Email Address, Primary Address
HAVING (Sum(1) > 1)

but it doesn't work. I get this message


So I changed it to this and it worked (Sort of)

SELECT [Patron Account Name], [Primary Phone Display], [Primary Email Address], [Primary Address 1], Sum(1) as CNT
FROM [All Accounts Since 2013]
GROUP BY [Patron Account Name], [Primary Phone Display], [Primary Email Address], [Primary Address 1]
HAVING (Sum(1) > 1)

I got this return, but I have no idea which column it is counting from, I want it to list the count for each column and list each occurrences.




Also when I try to sort the Patron Account Name Column I get this message again

[Gicu]

You would have to do it on field at the time, right now it gives you the count of records where all four fields are duplicated.

Cheers,

[thousman]

Thanks Gicu, so can I do it all in one query and why do I get this weird me4ssage Syntax Error on Patron Account Name?

[Gicu]

No, you need four queries, one for each field, and you do not show the error message, but it is likely caused by having spaces in the field names; open the query in SQL view and make sure you have the field name in the Order By clause wrapped in square brackets.

Cheers,