Extracting Unique/Duplicate list with count in access

[Nik]

Hi All,



I am facing a problem in running this query. I have around 90000 records in a table in access from which i want to extract only unique entry with their total counts. There are multiple row heading like name, presentaddress, fathername, form no, type etc. Basically criteria for finding duplicate is based on 3 fields out of all these which are name, address, fathername. I am running this query but getting error. If i delete the additional fields other than used for criteria from select statement, then its running fine but i need to display other fields also thats why included them but getting an error

SELECT [Bulk New].NAME, [Bulk New].FATHERNAME, [Bulk New].Presentaddress,[Bulk New].formno,[Bulk New].type, Count(*) AS [Count]
FROM [Bulk New]
GROUP BY [Bulk New].NAME, [Bulk New].FATHERNAME, [Bulk New].Presentaddress
HAVING (((Count(*))>1));

getting an error message " You tried to execute a query that does not include specified expression "Mobile number" as part of an agregate function.

Request anybody to pls tell the workaround for this.

Rgds
Nik

[NoellaG]

Hi,

when creating a groups query, all fields in it have to make part of the grouping clause or have an aggregate function. So, if you can't include them in the grouping statement you could use a MIN or MAX function with the field name.

greetings
NG