How to check for duplicates?

  • Thread starter Thread starter Amin
  • Start date Start date
A

Amin

So you have a table that has multiple fields. Records in one of the fields
duplicate. As in, in Field "A", there are the numbers 24, 56, 74, 24, 24.
What I would like to do is query ONLY the records that have duplicates so
that my query returns Field "A" with 24, 24, 24. How would I do this?

Thanks! I greatly appreciate any ideas!

Amin
 
Go to View-> Totals

This will gather all the similar types of data. Make sure you change the
"Group By" clause to whatever is needed for that field.
 
Yes, but I do not belive that is sufficient. If I do the following

SELECT (Fieldname) FROM [DATABASE] GROUP BY (Fieldname)

Using my hypothetical numbers, I would get 24, 56, 74. What I need is 24,
24, 24, with all of the other fields.

Amin
 
Ahhh... I didn't read it as clearly as I should've.

What you could do is set up a column in the query with a Dcount like

DCount("Field","Tbl","Where Field=[Query Field]");

And then in the criteria for that column put >1

Otherwise, im not too sure

Amin said:
Yes, but I do not belive that is sufficient. If I do the following

SELECT (Fieldname) FROM [DATABASE] GROUP BY (Fieldname)

Using my hypothetical numbers, I would get 24, 56, 74. What I need is 24,
24, 24, with all of the other fields.

Amin

akphidelt said:
Go to View-> Totals

This will gather all the similar types of data. Make sure you change the
"Group By" clause to whatever is needed for that field.
Click to expand...
Click to expand...
 
There is a built-in query wizard that will return the duplicates based
on a field.

The wizard builds a query that looks like

SELECT Y.*
FROM YourTable as Y
WHERE FieldA in
(SELECT FieldA
FROM YourTable As Y2
GROUP BY FieldA
HAVING COUNT(*) > 1)
ORDER BY FieldA

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Try this --
SELECT [Fieldname], Count([Fieldname]) AS FieldCount
FROM [DATABASE]
WHERE Count([Fieldname]) >1
GROUP BY [Fieldname];

--
KARL DEWEY
Build a little - Test a little


Amin said:
Yes, but I do not belive that is sufficient. If I do the following

SELECT (Fieldname) FROM [DATABASE] GROUP BY (Fieldname)

Using my hypothetical numbers, I would get 24, 56, 74. What I need is 24,
24, 24, with all of the other fields.

Amin

akphidelt said:
Go to View-> Totals

This will gather all the similar types of data. Make sure you change the
"Group By" clause to whatever is needed for that field.
Click to expand...
Click to expand...
 
Hi Karl,
When I try your code, i am prompted with a message that says the following:

'Cannot have aggregate function in your code 'WHERE Count([Fieldname]) >1'

Why can we not put "aggregate functions" in the Where clause?

Thanks,
Amin

KARL DEWEY said:
Try this --
SELECT [Fieldname], Count([Fieldname]) AS FieldCount
FROM [DATABASE]
WHERE Count([Fieldname]) >1
GROUP BY [Fieldname];

--
KARL DEWEY
Build a little - Test a little


Amin said:
Yes, but I do not belive that is sufficient. If I do the following

SELECT (Fieldname) FROM [DATABASE] GROUP BY (Fieldname)

Using my hypothetical numbers, I would get 24, 56, 74. What I need is 24,
24, 24, with all of the other fields.

Amin

akphidelt said:
Go to View-> Totals

This will gather all the similar types of data. Make sure you change the
"Group By" clause to whatever is needed for that field.

:

So you have a table that has multiple fields. Records in one of the fields
duplicate. As in, in Field "A", there are the numbers 24, 56, 74, 24, 24.
What I would like to do is query ONLY the records that have duplicates so
that my query returns Field "A" with 24, 24, 24. How would I do this?

Thanks! I greatly appreciate any ideas!

Amin
Click to expand...
Click to expand...
Click to expand...
 
Change to --
SELECT [Fieldname], Count([Fieldname]) AS FieldCount
FROM [DATABASE]
GROUP BY [Fieldname]
HAVING (Count([Fieldname]) >1);

--
KARL DEWEY
Build a little - Test a little


Amin said:
Hi Karl,
When I try your code, i am prompted with a message that says the following:

'Cannot have aggregate function in your code 'WHERE Count([Fieldname]) >1'

Why can we not put "aggregate functions" in the Where clause?

Thanks,
Amin

KARL DEWEY said:
Try this --
SELECT [Fieldname], Count([Fieldname]) AS FieldCount
FROM [DATABASE]
WHERE Count([Fieldname]) >1
GROUP BY [Fieldname];

--
KARL DEWEY
Build a little - Test a little


Amin said:
Yes, but I do not belive that is sufficient. If I do the following

SELECT (Fieldname) FROM [DATABASE] GROUP BY (Fieldname)

Using my hypothetical numbers, I would get 24, 56, 74. What I need is 24,
24, 24, with all of the other fields.

Amin

:

Go to View-> Totals

This will gather all the similar types of data. Make sure you change the
"Group By" clause to whatever is needed for that field.

:

So you have a table that has multiple fields. Records in one of the fields
duplicate. As in, in Field "A", there are the numbers 24, 56, 74, 24, 24.
What I would like to do is query ONLY the records that have duplicates so
that my query returns Field "A" with 24, 24, 24. How would I do this?

Thanks! I greatly appreciate any ideas!

Amin
Click to expand...
Click to expand...
Click to expand...
 
Back
Top