Query criteria, find multiple records only

[JanesC]

I have a query which records Computer Id Numbers. I want to find out which Computer Id Numbers occur most often in the database. E.g, I'm not interested in the Id Numbers that only occur once.
How can i get the query to return only the records that the user name appears more than once?

 

[B827]

Try

SELECT Computers.ComputerID, Count(Computers.ComputerID) AS ComputerCount
FROM Computers
GROUP BY Computers.ComputerID
HAVING (((Count(Computers.ComputerID))>1)); Sandy

 

[JanesC]

Thanks

Yes that has counted the number of times that the ComputerID appears, but i want to be able to view the Description for each occurance.

When i try to bring the Description field into that query, it only returns the Descriptions which match exactly.

How can i view the ComputerID and Description, but only for records where the ComputerID has appeared more than once?

I hope this makes sense?

 

[tlbroadbent]

Try this.

SELECT c.* FROM Computers As c
JOIN (SELECT Computers.ComputerID
FROM Computers
GROUP BY Computers.ComputerID
HAVING Count(Computers.ComputerID)>1) As q
On c.ComputerID=q.ComputerID; Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.