SQL searches

[Matt]

I've got a table with computer names and IP addresses in different columns.

I would like to do a search that returns all the computer names that aren't
unique (i.e if there are more than one computer with the same name).

Any ideas?

 

[Allen Browne]

1. Create a query using this table.

2. Depress the Total button on the toolbar/ribbon. (Upper sigma icon.)
Access adds a Total row to the query design grid.

3. Drag the ComputerName field into the grid.
In the Total row below this field, accept Group By.

4. Drag the primary key field into the grid.
In the Total row under this, choose Count.
In the Criteria row, enter:

= 2

The query returns the names where the count is 2 or more (i.e. there are
duplicates.)

If you wish, you can add the IP field, and choose Min to show the first IP
address for that computer name. Then add the IP field again, and choose Max
to show the last IP address for that computer name. (If there's more than 2,
you'll have to find the others yourself.)

 

[John Spencer]

If you want the individual records, there is query wizard that will generate a
query to return the individual records that are duplicates. The wizard will
generate a query that looks like the following:

SELECT *
FROM SomeTable
WHERE ComputerName in
(SELECT ComputerName
FROM SomeTable
GROUP BY ComputerName
HAVING Count(*) > 1)
ORDER BY ComputerName

The benefit of the above is that you can edit or delete the records that are
returned.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County