Duplicate Entries

[Marios]

Hello everyone out there

I have a table that has a lot of duplicate entries. It mas mainly two fields
an 'ID' field, and a Field_B.

Question 1: How can I delete duplicate entries;
Question 2: I want to run this SQL

SELECT Field_b, Count(ID)
FROM TAble
GROUP BY Field_b;

but due to duplicate entries I do not get proper results. DISTINCT does not
work. Is there a way to run this query and get results for unique records?

Thanks a lot

 

[John Spencer]

Question 1:
Is ID unique in the table?

If it is, how do decide which of the records you wish to keep? Or does that
now matter?

Question 2:
You need to tell us what you want as a result. I do not get proper results
does not tell us what you want and how that differs from what you are
getting.

If what you want is a count of how many different unique values there are
for Field_B, then this query with a subquery in the FROM clause may be the
solution for you. (Note: This will not work UNLESS you have named your
tables and fields with only Letters, Numbers, and the underscore - no other
characters allowed.)

SELECT Count(Tmp.Field_B) as CountB
FROM
(SELECT DISTINCT Field_B
FROM Table) as Tmp

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jason Lepack]

Hello everyone out there

I have a table that has a lot of duplicate entries. It mas mainly two fields
an 'ID' field, and a Field_B.

Question 1: How can I delete duplicate entries;
Question 2: I want to run this SQL

SELECT Field_b, Count(ID)
FROM TAble
GROUP BY Field_b;

but due to duplicate entries I do not get proper results. DISTINCT does not
work. Is there a way to run this query and get results for unique records?

Thanks a lot

Are you saying that each pair {ID, Field_B} is to be unique?

If so then after you fix your duplicate problem then these should be
indexed so that they do not allow duplicates.

You could create a summary query and group by both fields:
SELECT ID, Field_B
FROM Table
GROUP BY ID, Field_B

 

[Marios]

OK let me answer a few questions, so that I can make life easier:

Question 1:
Is ID unique in the table?

NO, is should be though

If it is, how do decide which of the records you wish to keep? Or does that
now matter?

Question 2:
You need to tell us what you want as a result. I do not get proper results
does not tell us what you want and how that differs from what you are
getting.

What I really want is to be able to count all distinct ID instances grouped
by Field_B. The SQL shall look like this:
SELECT Field_B, Count(DISTINCT ID) AS TotalID
FROM Table
WHERE DateField BETWEEN 01/01/01 and 02/02/02
GROUP BY FieldB;

 

[John Spencer]

IN Access SQL, this is going to require a subquery in the FROM clause.

SELECT B.Field_B, Count(B.ID) as TheCount
FROM (
SELECT DISTINCT Field_B, ID
FROM Table
WHERE DateField BETWEEN #01/01/01# and #02/02/02#) as B
GROUP BY B.Field_B


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..