Prev: Nested IIf and IsNull
Next: How could I SELECT the max of 2 different fields?
From: Matt on 24 Mar 2010 23:49
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?
From: Allen Browne on 25 Mar 2010 01:18
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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Matt" <Matt(a)discussions.microsoft.com> wrote in message
news:BC30E368-E732-456C-AE26-0B61715F282C(a)microsoft.com...
> 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?

From: John Spencer on 25 Mar 2010 14:22
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

Allen Browne wrote:
> 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.)
>
 | 
Pages: 1
Prev: Nested IIf and IsNull
Next: How could I SELECT the max of 2 different fields?