PLEASE !!! I need a query for duplicates

  • Thread starter Thread starter giannis
  • Start date Start date
G

giannis

I need a querry that contain only
the duplicates records for a field.
That is if there is 3 duplicates for a field the
query must display these 3 records.
I need this query so i can delete the duplicate
records that dont want.
The table is "T" and the field is "f".
What is the query ?
Can i use the DCount() function ?
But how ?
 
Have you tried the "Find Duplicates Query" Wizard?

In the Queries Container window, click New the
select "Find Duplicates Query Wizard".

HTH
Van T. Dinh
MVP (Access)
 
This field is a text field. When i run this second query
i receive message error when the text of field contain
a " ' " (quotemark) !!!
Why and how resolved this problem ?

The query of Peter is running well :
SELECT DISTINCTROW [p]
FROM Software
WHERE [p] In (SELECT [p] FROM [Software] As Tmp
GROUP BY [p] HAVING Count(*)>1 )
ORDER BY [p]



John Vinson said:
I need a querry that contain only
the duplicates records for a field.
That is if there is 3 duplicates for a field the
query must display these 3 records.
I need this query so i can delete the duplicate
records that dont want.
The table is "T" and the field is "f".
What is the query ?
Can i use the DCount() function ?
But how ?
Click to expand...

Try this:

SELECT * FROM t
WHERE DCount("*", "[T]", "[f] = " & [f]) > 1;

If f is a Text field you need quotemarks:

SELECT * FROM t
WHERE DCount("*", "[T]", "[f] = '" & [f] & "'") > 1;
Click to expand...
 
This field is a text field. When i run this second query
i receive message error when the text of field contain
a " ' " (quotemark) !!!
Click to expand...

Use " to delimit rather than ':

SELECT * FROM t
WHERE DCount("*", "[T]", "[f] = """ & [f] & """") > 1;
 
i receive message error when the text of field
contain a " or a ' !!!
Click to expand...

Yes. You will.

Can your text values contain any combination of ' and " characters? If
so you will need some VBA code. Perhaps simplest is to use ' as a
delimiter, and

Replace([f], "'", "''")

as the criterion. This will change

'Lou's Diner'

to

'Lou''s Diner'

The doubled quote will be treated as a single ' character.
 
Back
Top