Queries: Display only if duplicates exist

[CharlieT302]

Hi folks,

I would like my query to display records only if two or more records exist for a client number.

Any thoughts?

Thanks

 

[PHV]

As you explained nothing about your actual tables schema, only a starting point (SQL code):
SELECT * FROM yourTable
WHERE [client number] In (SELECT [client number] FROM yourTable GROUP BY [client number] HAVING Count(*)>1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

in order to filter on an aggregate function, like SUM, COUNT, AVG, you have to using the HAVING clause as opposed to the WHERE clause:

SELECT ClientNumber, COunt(*) From TableName Group BY ClientNumber Having COUNT(*) > 1



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[CharlieT302]

Basically, I want to group all values based on the Client Number. If a group consists of 2 or more records, I want to see both records.

The real problem is that the statement we used originally does not work when the backend data is stored on an SQL server.

Here is the original criteria:
In (SELECT [CLIENT_NUMBER] FROM [Qry_Report_ODA_Waiting_List_Names_ON] As Tmp GROUP BY [CLIENT_NUMBER] HAVING Count(*)>1 )


I am trying to find a simpler method.

 

[PHV]

And what is Qry_Report_ODA_Waiting_List_Names_ON ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CharlieT302]

Qry_Report_ODA_Waiting_List_Names_ON is the name of the underlying data source. Its a query that is stored on the SQL server.