Querying for duplicate records...

[Goobz]

Can anyone tell me if there is a way of running a query specifically
for duplicate records!?

I have Field1 which is a numerical field (no duplicates), and Field2
which is a General field (duplicates allowed). I was wondering if
there's a way to run a query to only display records where Field2
exists (in whole, or part) in multiple records!?

Thanx!

 

[KARL DEWEY]

Try this as it seems to be what you are asking for ---
SELECT Your_Table.x, Your_Table_1.x
FROM Your_Table, Your_Table AS Your_Table_1
WHERE (((Your_Table_1.x) Is Not Null) AND
((Your_Table.ID)<>[Your_Table_1].[ID]))
GROUP BY Your_Table.x, Your_Table_1.x
HAVING (((Your_Table.x) Like "*" & [Your_Table_1].[x] & "*"));

 

[Michael Gramelspacher]

Can anyone tell me if there is a way of running a query specifically
for duplicate records!?

I have Field1 which is a numerical field (no duplicates), and Field2
which is a General field (duplicates allowed). I was wondering if
there's a way to run a query to only display records where Field2
exists (in whole, or part) in multiple records!?

Thanx!

perhaps:

SELECT m.record_nbr,
m1.my_name,
m.my_name
FROM MyTables AS m
INNER JOIN MyTable AS m1
ON m.record_nbr <> m1.record_nbr
AND m.my_name LIKE "*" & m1.my_name & "*";

record_nbr m1.my_name m.my_name
6 Steven Stevenson
7 Steven Stevensohn
8 Adam Adamson
9 Adam Adamhorst
2 Albert Albertson
3 Albert Albertsen
4 Albert VonAlbert

 

[Goobz]

Try this as it seems to be what you are asking for ---
SELECT Your_Table.x, Your_Table_1.x
FROM Your_Table, Your_Table AS Your_Table_1
WHERE (((Your_Table_1.x) Is Not Null) AND
((Your_Table.ID)<>[Your_Table_1].[ID]))
GROUP BY Your_Table.x, Your_Table_1.x
HAVING (((Your_Table.x) Like "*" & [Your_Table_1].[x] & "*"));

--
KARL DEWEY
Build a little - Test a little

Can anyone tell me if there is a way of running a query specifically
for duplicate records!?

I have Field1 which is a numerical field (no duplicates), and Field2
which is a General field (duplicates allowed). I was wondering if
there's a way to run a query to only display records where Field2
exists (in whole, or part) in multiple records!?

Thanx!- Hide quoted text -

- Show quoted text -

Okay.. You've got me scratching my head on this one... The duplicates
I am trying to find will all be in the same table (DN) under the
DisplayName field. Basically, I want to find all the users that have
more than one entry in the table, and return their extension number
(DN.DN), and the name that is shown as a duplicate (DN.DisplayName).

 

[KARL DEWEY]

You orignally said "to only display records where Field2 exists (in whole, or
part) in multiple records!?"

Now you say "I want to find all the users that have more than one entry in
the table, and return their extension number (DN.DN), and the name that is
shown as a duplicate (DN.DisplayName)."

For me to understand please post example of data in the table and what the
query needs to return.
--
KARL DEWEY
Build a little - Test a little

Try this as it seems to be what you are asking for ---
SELECT Your_Table.x, Your_Table_1.x
FROM Your_Table, Your_Table AS Your_Table_1
WHERE (((Your_Table_1.x) Is Not Null) AND
((Your_Table.ID)<>[Your_Table_1].[ID]))
GROUP BY Your_Table.x, Your_Table_1.x
HAVING (((Your_Table.x) Like "*" & [Your_Table_1].[x] & "*"));

--
KARL DEWEY
Build a little - Test a little

Can anyone tell me if there is a way of running a query specifically
for duplicate records!?

I have Field1 which is a numerical field (no duplicates), and Field2
which is a General field (duplicates allowed). I was wondering if
there's a way to run a query to only display records where Field2
exists (in whole, or part) in multiple records!?

Thanx!- Hide quoted text -

- Show quoted text -

Okay.. You've got me scratching my head on this one... The duplicates
I am trying to find will all be in the same table (DN) under the
DisplayName field. Basically, I want to find all the users that have
more than one entry in the table, and return their extension number
(DN.DN), and the name that is shown as a duplicate (DN.DisplayName).

 

[Goobz]

You orignally said "to only display records where Field2 exists (in whole, or
part) in multiple records!?"

Now you say "I want to find all the users that have more than one entry in
the table, and return their extension number (DN.DN), and the name that is
shown as a duplicate (DN.DisplayName)."

For me to understand please post example of data in the table and what the
query needs to return.
--
KARL DEWEY
Build a little - Test a little

Try this as it seems to be what you are asking for ---
SELECT Your_Table.x, Your_Table_1.x
FROM Your_Table, Your_Table AS Your_Table_1
WHERE (((Your_Table_1.x) Is Not Null) AND
((Your_Table.ID)<>[Your_Table_1].[ID]))
GROUP BY Your_Table.x, Your_Table_1.x
HAVING (((Your_Table.x) Like "*" & [Your_Table_1].[x] & "*"));
--
KARL DEWEY
Build a little - Test a little
:
Can anyone tell me if there is a way of running a query specifically
for duplicate records!?
I have Field1 which is a numerical field (no duplicates), and Field2
which is a General field (duplicates allowed). I was wondering if
there's a way to run a query to only display records where Field2
exists (in whole, or part) in multiple records!?
Thanx!- Hide quoted text -
- Show quoted text -

Okay.. You've got me scratching my head on this one... The duplicates
I am trying to find will all be in the same table (DN) under the
DisplayName field. Basically, I want to find all the users that have
more than one entry in the table, and return their extension number
(DN.DN), and the name that is shown as a duplicate (DN.DisplayName).- Hide quoted text -

- Show quoted text -

I apologize. I believe I said the same thing, just explained it
wrong! Here would be my results...

DN.DN DN.DisplayName
1234 David Man
1235 David Man Cell
1236 David Man
1283 David Man
2345 Weird Oh
5665 Weird Oh
8234 Weird Oh Cell

That's basically what I am looking for. BOTH those fields (DN &
DisplayName) appear in the same table (DN).

 

[KARL DEWEY]

Try this ---
SELECT DN.DN, DN.DisplayName
FROM DN, DN AS DN_1
WHERE (((DN.DisplayName) Like "*" & [DN_1].[DisplayName] & "*") AND
((DN.DisplayName) Is Not Null) AND ((DN.DN)<>[DN_1].[DN]))
GROUP BY DN.DN, DN.DisplayName;

--
KARL DEWEY
Build a little - Test a little

You orignally said "to only display records where Field2 exists (in whole, or
part) in multiple records!?"

Now you say "I want to find all the users that have more than one entry in
the table, and return their extension number (DN.DN), and the name that is
shown as a duplicate (DN.DisplayName)."

For me to understand please post example of data in the table and what the
query needs to return.
--
KARL DEWEY
Build a little - Test a little

On Dec 19, 3:35 pm, KARL DEWEY <[email protected]>
wrote:
Try this as it seems to be what you are asking for ---
SELECT Your_Table.x, Your_Table_1.x
FROM Your_Table, Your_Table AS Your_Table_1
WHERE (((Your_Table_1.x) Is Not Null) AND
((Your_Table.ID)<>[Your_Table_1].[ID]))
GROUP BY Your_Table.x, Your_Table_1.x
HAVING (((Your_Table.x) Like "*" & [Your_Table_1].[x] & "*"));

:
Can anyone tell me if there is a way of running a query specifically
for duplicate records!?

I have Field1 which is a numerical field (no duplicates), and Field2
which is a General field (duplicates allowed). I was wondering if
there's a way to run a query to only display records where Field2
exists (in whole, or part) in multiple records!?

Thanx!- Hide quoted text -

- Show quoted text -

Okay.. You've got me scratching my head on this one... The duplicates
I am trying to find will all be in the same table (DN) under the
DisplayName field. Basically, I want to find all the users that have
more than one entry in the table, and return their extension number
(DN.DN), and the name that is shown as a duplicate (DN.DisplayName).- Hide quoted text -

- Show quoted text -

I apologize. I believe I said the same thing, just explained it
wrong! Here would be my results...

DN.DN DN.DisplayName
1234 David Man
1235 David Man Cell
1236 David Man
1283 David Man
2345 Weird Oh
5665 Weird Oh
8234 Weird Oh Cell

That's basically what I am looking for. BOTH those fields (DN &
DisplayName) appear in the same table (DN).