How to check for duplicates?

[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

 

[akphidelt]

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.

 

[Amin]

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]

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

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.

 

[John Spencer]

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
'====================================================

 

[KARL DEWEY]

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

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.

 

[Amin]

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

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

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

 

[KARL DEWEY]

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

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

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

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